SQL 2012+

Using Sequences in SQL Server

by Richard Carr, published at http://www.blackwasp.co.uk/SQLSequence.aspx

SQL Server 2012 introduced the concept of sequences. These allow the automatic generation of series of numbers. They are similar to identity columns but do not need to be linked to a column.

Changing the Data Type

You can change the data type for the values returned by a sequence to any integer type, including a decimal or numeric type with zero decimal places. This allows you to choose the most appropriate type for most scenarios. To set the type, add the AS clause and the data type to the command.

The following statement creates a default sequence of tiny integers. The starting point is the lowest possible value for the type, which is zero.

CREATE SEQUENCE SequenceTiny
AS TINYINT

SELECT NEXT VALUE FOR SequenceTiny  -- 0
SELECT NEXT VALUE FOR SequenceTiny  -- 1
SELECT NEXT VALUE FOR SequenceTiny  -- 2

DROP SEQUENCE SequenceTiny

Limitations

There are some limitations that should be considered when using sequences. Firstly, there is nothing preventing a cyclic sequence from generating duplicate values. If you are using these values in a table that should not permit duplicates, you should add a unique index or unique constraint.

Sequences use a caching mechanism that increases performance. When you request a value, it is retrieved from several that are preloaded into memory. The next value, following the cached items, is stored in the database files. If there is a problem that causes SQL Server to terminate unexpectedly, the cache will be lost and the next requested value will be the one that was stored on disk. This can create gaps in the sequence. You can prevent this with the NO CACHE clause, which disables the caching process. However, this may slow the performance.

Reading a value from a sequence is not part of the current transaction. If a transaction rolls back after requesting the next item, this value may never be used. The following request will return the next number from the sequence. This can cause gaps in the results.

When you use the NEXT VALUE FOR command twice in a single statement, both return the same value.