SQL Server 2012 - SEQUENCE

SQL Server 2012 introduces the SEQUENCE object. Looking at the syntax it is very similar to how Oracle has implemented SEQUENCEs for many years. I believe that the SEQUENCE was introduced to aid in the transition of Oracle developers to SQL Server.

First off what is a SEQUENCE… A user-defined object that generates a sequence of numeric values.

To create a SEQUENCE you can specify one or more of the following options:

Start
Increment
Min Value, Max Value
Cycle / No Cycle – starts of when the Max value is hit or not
Cache

The SEQUENCE is an alternative to IDENTITY. SQL Server has had the IDENTITY option for many years.

Here is an example of using a sequence:


CREATE SEQUENCE myTestSequence
 START WITH 0;
select NEXT VALUE FOR myTestSequence;

select NEXT VALUE FOR myTestSequence;

select NEXT VALUE FOR myTestSequence;

Similar to an identity, but you have more control over it, and it can be accessed from multiple tables, and you have access to it prior to inserting it into a table.

There may be gaps created when using a sequence.

Other people / processes can use your SEQUENCE with you knowing it, this will cause gaps
Rolled back transactions that ask for the NEXT VALUE and don’t use it may cause gaps.

You should use a SEQUENCE Instead of IDENTITY When…

The application requires a number before the insert is run.
Sharing a single series of numbers between multiple tables or multiple columns within a table is needed.
The application must restart the number series at some point.
The application requires sequence values to be sorted by another field. The NEXT VALUE FOR function can apply the OVER clause to the function call.
You need to change the specification of the sequence, such as the increment value.

For more info on sequences, take a look at my presentation for SQL Saturday 166 in Olympia WA.

SQL Server 2012 – SEQUENCE

You should use a SEQUENCE Instead of IDENTITY When…

Leave a Reply Cancel reply