SEQUENCES IN SQL Server 2012

At long last SQL Server gets Sequences. The community had been asking for this ANSI feature for some time now. ORACLE has had this since its inception.

A sequence is simply a user-defined object that is used to generate a number sequence according to a set of rules that are specified when the sequence in created.

The rules that govern sequence creation are as follows:

• Create sequences in ascending or descending order
• Control the starting point and intervals
• Specify the maximum and minimum values
• Be configured to restart (or cycle) when exhausted
• Cache sequence values to improve performance.

Applications can obtain the next sequence number by calling the NEXT VALUE FOR function.

The syntax for creating sequences is as follows:

 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 } ]  
   [ ; ]  

Consider the following sequences:

 Create Schema SeqTest;  
 CREATE SEQUENCE SeqTest.Counter1  
   START WITH 1  
   INCREMENT BY 1  
 ;  
 Create SEQUENCE SeqTest.Counter100  
      Start with 100  
      INCREMENT BY 50  
      MINVALUE 100  
      MAXVALUE 300  
      CYCLE  
      CACHE 3  
 ;  

SeqTest.Counter1 creates a sequence that starts with 1 and increments by 1 whenever the NEXT VALUE FOR function is called.
The second sequence starts with 100 and increments by 50. Once 300 has been reached, the sequence restarts from 100.

Let us try this out:

 CREATE TABLE SeqTest.Person  
   (  
           ID int PRIMARY KEY,  
           Name varchar(20) NOT NULL,  
           Ranking int  
   );  
 GO  
 INSERT SeqTest.Person (ID, Name,Ranking)  
   VALUES (NEXT VALUE FOR SeqTest.Counter1, 'John', Next Value for SeqTest.Counter100) ;  
 INSERT SeqTest.Person (ID, Name,Ranking)  
   VALUES (NEXT VALUE FOR SeqTest.Counter1, 'Jane', Next Value for SeqTest.Counter100) ;  
 INSERT SeqTest.Person (ID, Name,Ranking)  
   VALUES (NEXT VALUE FOR SeqTest.Counter1, 'Bill', Next Value for SeqTest.Counter100) ;  
 INSERT SeqTest.Person (ID, Name,Ranking)  
   VALUES (NEXT VALUE FOR SeqTest.Counter1, 'Ann', Next Value for SeqTest.Counter100) ;  
 INSERT SeqTest.Person (ID, Name,Ranking)  
   VALUES (NEXT VALUE FOR SeqTest.Counter1, 'Jack', Next Value for SeqTest.Counter100) ;  
 INSERT SeqTest.Person (ID, Name,Ranking)  
   VALUES (NEXT VALUE FOR SeqTest.Counter1, 'Jill', Next Value for SeqTest.Counter100) ;  
 INSERT SeqTest.Person (ID, Name,Ranking)  
   VALUES (NEXT VALUE FOR SeqTest.Counter1, 'Frank', Next Value for SeqTest.Counter100) ;

select * from SeqTest.Person 

ID	Name	Ranking
1	John	100
2	Jane	150
3	Bill	200
4	Ann	250
5	Jack	300
6	Jill	100
7	Frank	150

Data Types
Sequences can be used with the following data types :
tinyint, smallint, int, bigint, decimal and numeric( scale of 0). If the data type is not specified, it defaults to bigint.

Resetting Sequences

Sequence can be reset as follows:

 ALTER SEQUENCE SeqTest.Counter1  
 RESTART WITH 1 ;  

Cache and No Cache Option
The cache option is used to increase performance by caching the current value and number of values left in the cache. This minimizes the number of disk IOs required to generate sequences.

Transaction consistency
Sequences are not transactionally consistent and run outside the scope of the current transaction. So it is possible for gaps to appear in the sequence. If a transaction is rolled back, the next value is still taken.

 CREATE SEQUENCE SeqTest.Counter2 AS smallint    
      START WITH 1    
      MAXVALUE 100   
      ;  
 BEGIN TRANSACTION;    
 SELECT NextBar = NEXT VALUE FOR SeqTest.Counter2; --displays 1 
 ROLLBACK TRANSACTION;  
 SELECT NextBar = NEXT VALUE FOR SeqTest.Counter2;  --displays 2

Stay tuned. In my next post, I will look at when to choose Sequences over Identity columns.
James