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:
- Min Value, Max Value
- Cycle / No Cycle – starts of when the Max value is hit or not
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;
- 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.