A long time developer's demands for an object that can generate sequential number like ORACLE have. After long journey with MS SQL server MS finally think about it and provide SEQUENCE objects in MS SQL Server 2012.
In this article I am trying to understand the feature of SEQUENCE objects of MS SQL 2012.
What About IDENTITY
As the developers knows INDENTITY columns very well (it starts from MS SQL 2000) but there are lot of difference with SEQUENCE.
First we have to understand although the both IDENTITY and SEQUENCE generate sequential number but there are certain differences between them.
SEQUENCE is a database object but IDENTITY columns are tied to table. That means we can use the SEQUENCE objects in entire database. A single SEQUENCE object can be used with multiple Table objects to insert sequential values.
We can use SEQUENCE objects with T-SQL statement also.
How we can Create and use the SEQUENCE object
To use the SEQUENCE objects we must first create it
CREATE SEQUENCE [dbo].[MySequence] AS INT
START WITH 1
INCREMENT BY 1
CREATE TABLE dbo.Tbl_Employee(ID INT, EmpName VARCHAR(50))
INSERT INTO dbo. Tbl_Employee
VALUES (NEXT VALUE FOR dbo.MySequence,'Joydeep Das'),
(NEXT VALUE FOR dbo.MySequence,'Archita Dutta'),
(NEXT VALUE FOR dbo.MySequence,'Rajesh Das')
SELECT * FROM dbo.Tbl_Employee WITH(NOLOCK)
1 Joydeep Das
2 Archita Dutta
3 Rajesh Das
Regenerate SEQUENCE Number
ALTER SEQUENCE dbo.MySequence
RESTART WITH 1 ;
SELECT (NEXT VALUE FOR dbo.MySequence)
Get the Current value of SEQUENCE
Hope you like it.
Posted by: MR. JOYDEEP DAS