SQL Server 2012 – SEQUENCE

Download PDF

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.


					
									
Tagged with: , , , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *

*

Time limit is exhausted. Please reload CAPTCHA.