In SQL Server 2012 the SEQUENCE object was added. This seems to be to get past some of the deficiencies in the IDENTITY property.
It allows you to use the same sequence across multiple columns in multiple tables whereas the IDENTITY property is linked to a particular column within a particular table.
It allows cycling of the sequence and allows you to create the next sequence number before you insert the data into the table. With the IDENTITY property you need to insert the data first and then get the newly generated identity value.
To create a new SEQUENCE object just use the following format
CREATE SEQUENCE dbo.SimonTest
This creates a new SEQUENCE object named dbo.SimonTest with default properties, including the default BIGINT data type.
The SEQUENCE object has the following properties
- INCREMENT BY – the value to increment by. Equivalent to the y value in the IDENTITY(x,y) property. The default is 1.
- MINVALUE – the minimum value allowed. By default this is this smallest value allowed in the data type.
- MAXVALUE – the maximum value allowed. By default this is the largest value allowed in the data type.
- CYCLE or NOCYCLE – says whether to allow cycling or not. The default is NOCYCLE.
- START WITH – the value to start the sequence at. Equivalent to the x value in the IDENTITY(x,y) property.
Cycling is where the SEQUENCE object will reset itself to the minimum value once it reaches the maximum value – I’m not sure when you would actually use this. It’s worth noting that if you want your sequence to allow cycling and you only want to include positive numbers then you should set the MINVALUE propery to be 1, otherwise if you’re using an INT data type the first value after cycling will be -2147483648 even if you set the START WITH property to 1. For example
CREATE SEQUENCE dbo.SimonPositiveCycle AS INT MINVALUE 1 CYCLE;
will create a new SEQUENCE object with an INT data type. The START VALUE is by default the same as the MINVALUE so this will this 1 and when it recycles it will go back to 1.
Now we want to start using the SEQUENCE and to do this we run
SELECT NEXT VALUE FOR dbo.SimonPositiveCycle
This will return the value 1. If we run it again it returns 2 and so on.
You can use the SELECT NEXT VALUE FOR function in multiple places including INSERT and UPDATE statements and default constraints. To use a SEQUENCE object instead of an IDENTITY property you just create a default constraint on the identity column.
All of the SEQUENCE object properties listed above can be updated using the ALTER SEQUENCE statement.
Finally, you can tell the SEQUENCE object how often it should write the current sequence value to disk rather than just storing it in memory.
ALTER SEQUENCE dbo.SimonPositiveCycle CACHE 100;
means that it will write to memory 100 times before it writes to the disk. It’s better for performance to write to memory but there is a risk that you lose the current value if there is a shutdown.