Sequence Object in SQL Server 2012

MS SQL Server has introduced a new feature of Sequence Objects in SQL Server 2012. In the prior versions, IDENTITY was used to generate sequence numbers. The limitation of IDENTITY was that it was related to one table. The scope of Sequence Objects is database. So, if you want to add a same value to two tables, you can use this feature. Also, you can generate the next sequence number before going into the transaction. The IDENTITY is incremented in the transaction at the time of INSERT. Another plus point of Sequence Object is that you can define the maximum value for the object and re-initialize the counter of the object without any extra programming effort.

Here is the code of creating teh new sequence object in SQL Server 2012 Management Studio;

CREATE SEQUENCE dbo.NextCustomerID
    as BIGINT
    START WITH 1
    INCREMENT BY 1;
GO

This will add a new sequence object. You can see this object in Object Explorer under your database in Programming --> Sequence folder.

Well, it not over yet. The query used to create this object creates the object with the default values. You can set many parameters. If you right click the newly created object and click properties, this will open the property window for this object. Here you can set Minimum value, Maximum Value, Cycle and Cache.

Minimum and Maximum are the minimum and maximum values for the object. Cycle check box states that the value will re-initialize when the maximum value is reached. You can also set the re-start value (From where it will start after reaching the maximum value). Cache option tells the object how many values it will have remaining to avoid extra Disk I/Os during the increment process. For example, if cache size is set to 25, it will  not generate 25 numbers in it memory. It will only keep the current value and the remaining values. If 3 IDs are generated for the object with cache of 25, it will have 4 and 22 in its memory. For more information on cache mechanism you can Click Here and check the Cache Management section.

Ok, here is the code for generating the sequence number from the object;

-- Get the next available ID
DECLARE @CustID BIGINT;
SELECT @CustID = NEXT VALUE FOR dbo.NextCustomerID;
SELECT @CustID as 'Customer ID';

Please remember that the sequence object is not transaction specific. Once you generate the sequence with the NEXT VALUE, it is consumed weather the transaction is committed and rolled back. This is similar to the IDENTITY. Hence, the sequence objects also may have gaps in between as we have in IDENTITY columns.

In case you want to restart the sequence, you can use;

ALTER SEQUENCE dbo.NextCustomerID

RESTART

You can use the OVER ORDER BY clause with NEXT VALUE FOR for generating the next sequence values;

SELECT NEXT VALUE FOR dbo.NextCustomerID OVER (ORDER BY RNO) AS ListNumber,

    Name

FROM tblTest ;

You can check books online for more information.

There are also the limitations of the Sequence Objects which I will cover in the separate topic and try to explain with examples.