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…

Leave a comment

Your email address will not be published.