SEQUENCE in SQL 2012

 

SQL Server 2012 code name "Denali" introduced a new feature of T-SQL to make task easier. It is called Sequence. It generates a sequence of number.

 In previous version we can specify identity fields in a table. But if we want to have database wide sequential number, then we must derive something by our self before SQL server 2012.

It was a long request by SQL community and Microsoft release it with version of SQL Server 2012.

The syntax is mentioned bellow:

CREATE SEQUENCE [schema_name . ] sequence_name

    [ AS [ built_in_integer_type | user-defined_integer_type ] ]

    [ START WITH <constant> ]

    [ INCREMENT BY <constant> ]

    [ { MINVALUE [ <constant> ] } | { NO MINVALUE } ]

    [ { MAXVALUE [ <constant> ] } | { NO MAXVALUE } ]

    [ CYCLE | { NO CYCLE } ]

    [ { CACHE [ <constant> ] } | { NO CACHE } ]

    [ ; ]

 

A partial list of the output demonstrates the default values.

start_value	-9223372036854775808
increment	1
mimimum_value	-9223372036854775808
maximum_value	9223372036854775807
is_cycling	0
is_cached	1
current_value	-9223372036854775808

 

Example to create Sequence is mentioned bellow.

CREATE SEQUENCE mysequence

START WITH 1

INCREMENT BY 1;

 

GO

 

BEGIN TRAN

SELECT NEXT VALUE FOR dbo.mysequence

ROLLBACK TRAN

Another example with Table objects are mentioned bellow.

----Create Sequence Object

CREATE SEQUENCE my_seq

START WITH 1

INCREMENT BY 1;

 

----Create Temp Table

DECLARE @Customer_dtl TABLE

            (

                  ID int NOT NULL PRIMARY KEY,

                  FullName nvarchar(100) NOT NULL

            );

 

----Insert Some Data

INSERT @Customer_dtl (ID, FullName)

VALUES (NEXT VALUE FOR my_seq, 'Joydeep Das'),

       (NEXT VALUE FOR my_seq, 'Sudip Das'),

       (NEXT VALUE FOR my_seq, 'Subarata Kar');

 

----Show the Data

SELECT * FROM @Customer_dtl;

The "NEXT VALUE FOR" T-SQL key words is used to get the next sequential number from sequence

 

Hope you like it.

 

Posted by: MR. JOYDEEP DAS