SEQUENCE in SQL Server 2012

SQL Server 2012 (or Denali) has now arrived CTP. In this
article I will look at a core new feature of SQL Server 2012 which is SEQUENCE.
Well, if you are familiar with Oracle, you will already know all about this
feature since it has been standard on Oracle more than 10 years I guess.

What is Sequence in SQL Server ?

In simple terms, it is a new database object and a substitute
for the Identity of columns.

Using the identity attribute for a column, you can easily
generate auto-incrementing numbers (which as often used as a primary key). With
Sequence, it will be a different object which you can attach to a table column
while inserting. Unlike identity, the next number for the column value will be retrieved
from memory rather than from the disk – this makes Sequence significantly
faster than Identity. We will see this in coming examples.

Creating a Sequence in SQL Server

To use Sequence first SQL Server Management Studio (SSMS)
and expand the Object explorer, under programmability you will see the sequence

If you right click the sequence and select new, you will be
taken to the below screen which has all the attributes for the sequence.

Since Sequence is a database object, it needs to be assigned
to a schema. It has a data type which can be int, bigint, tinyint,
smallint,numeric or decimal. The start value and increment as similar as to the
values you will be familiar with using Identity.

The Minimum and maximum are boundaries for the sequence. When
 the cycle option is set you have the ability to re-use sequence numbers. 

Similarly, Sequences can be created using T-SQL as follows.

IF EXISTS (SELECT * FROM sys.sequences WHERE name =
                DROP SEQUENCE EmployeeSeq;

 CREATE SEQUENCE EmployeeSeq AS tinyint
                START WITH 0
                INCREMENT BY 5;

Now let us see how we can integrate this with an Insert statement.

First we will create a table to incorporate the sequence we


(ID tinyint,  Name varchar(150) )

Then we will insert:


(NEXT VALUE FOR EmployeeSeq, ‘Dinesh’)


(NEXT VALUE FOR EmployeeSeq, ‘Asanka’)

Note that you are now using the EmployeeSeq sequence object
for the insert.


Pages: 1 2 3


3 Responses to “SEQUENCE in SQL Server 2012”

  1. Nice article , Except performance why I would use this function?

    any practical Scene in which i can see the usage.

    • One scenario that I can think of is to coordinate identity values across multiple tables. If you have a parent table with two child tables for example, you would normally insert the parent rec, return the identity value, then insert the child rec. However, if the only reason the parent table exists is to make sure the child IDs are unique, you could use a sequence instead and get rid of the overhead of the parent table.

      Not sure if this is practical or not, just the first thing that came to mind.

    • It is very useful in ORM based applications where you need to know what is the ID of saved entity before commiting the transaction.

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |