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
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,
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.