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 node.

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 = N’EmployeeSeq’)
                DROP SEQUENCE EmployeeSeq;
GO

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

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

First we will create a table to incorporate the sequence we created.

CREATE TABLE Employee
(ID tinyint,  Name varchar(150) )

Then we will insert:

INSERT INTO Employee
(ID,Name)
VALUES
(NEXT VALUE FOR EmployeeSeq, ‘Dinesh’)
INSERT INTO Employee
(ID,Name)

VALUES
(NEXT VALUE FOR EmployeeSeq, ‘Asanka’)

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

Continues…

Pages: 1 2 3




Related Articles :

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 |