SQL Server SEQUENCE

In 2012 SQL Server introduced 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.

 

---

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!