SQL Server 2012 Features - Sequence Number

Today,i have installed to explore the features of SQL Server 2012. First feature which i am going to discuss is "Sequence Number". You can generate sequence number like identity column. In Previous release, if you want to generate a sequence id before inserting into the table, you have to create a seperate physical table with identity column. Before inserting record into table, we have to insert a row in custom sequence table and get the latest value.

But in SQL Server 2012, you no need to insert record in any physical custom table to retrieve the number in sequence. Just you have to create sequence object which binds to schema.

Here are the steps to create Sequence object using SQL Server Management Studio.

1. Create a New Sequence:



2. Specify the NAME, MIN, MAX, CYCLE and OTHER PROPERTIES:



3. Once you entered the values for sequence object. You have to use following query to get NEXT sequence number using NEXT VALUE FOR keyword.

 select NEXT VALUE FOR dbo.GlobalSequence;  

RESULT:

 1  

If you execute above query, you will get the value as 1. Next time, if you executing same query, you will get the value as 2. Same way, you would get the number in sequential for every execution.

4. With CYCLE OPTION:

 select NEXT VALUE FOR dbo.GlobalSequence;  
 select NEXT VALUE FOR dbo.GlobalSequence;  
 select NEXT VALUE FOR dbo.GlobalSequence;  
 select NEXT VALUE FOR dbo.GlobalSequence;  
 select NEXT VALUE FOR dbo.GlobalSequence;  
 select NEXT VALUE FOR dbo.GlobalSequence;  

if you execute the above query, you would get values like below.

 1  
 2  
 3  
 4  
 5  
 1  

if sequence value reaches maximum value, it will start again with start value if you enabled the CYCLE option. Otherwise, it will throw an error like below;

 The sequence object 'GlobalSequence' has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.