Sequence ID generation is one of the common problems faced by SQL Server development community especially with high concurrent user scenarios. Though
SQL Server supports different work arrounds currently, each one has its own pros
and cons.
Microsoft has finally put an end to the long wait and
introduced the SEQUENCE object in its latest version SQL Server 2012 (Similar to the Oracle Sequences).
SEQUENCE is a user defined object type which generates a
sequence of numbers based on the increment definition.I have given the syntax and example below
SEQUENCE Syntax
CREATE
SEQUENCE [SequenceName]
START WITH [StarNumber]
INCREMENT BY [IncrementNumber];
START WITH [StarNumber]
INCREMENT BY [IncrementNumber];
SEQUENCE Example
CREATE
SEQUENCE EmployeeID
START WITH 1
INCREMENT BY 1;
START WITH 1
INCREMENT BY 1;
INSERT INTO EmployeeTable ( ID ,Name, Age
,Sex)
SELECT NEXT VALUE FOR EmployeeID ,’sam’ ,’30’,’Male’
For generating Sequence on older version of SQL Server , refer to my earlier article http://www.samsudeenb.blogspot.in/2009/06/how-to-generate-sequence-number-in-sql.html
