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.
- 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.
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!