Sequence in SQL Server 2011

This article will explain the use of Sequence in SQL server and its advantages over an Identity column.

Introduction:

Sequence in SQL server is a new database object and a substitute for the Identity columns.

Using the identity attribute for a column, we can easily generate auto-incrementing numbers, which are 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, and which is the main advantage of Sequence.

We can see Sequence in object explorer at the below path

In object explorer, Databases-> particular database->Programmability->Sequences.

Explanation:

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.

Below is query to create sequence

CREATE SEQUENCE StudentIDSeq AS int
                START WITH 1           
            INCREMENT BY 1;

Now let's see how to use sequence in an Insert statement.

First we will create a table to use the sequence we have created.

CREATE TABLE StudentData
(ID int,  Name varchar(150) )

Now let's insert to it using sequence:

INSERT INTO StudentData (ID,Name)
VALUES (NEXT VALUE FOR StudentIDSeq, 'Pravin')

INSERT INTO StudentData(ID,Name)
VALUES (NEXT VALUE FOR StudentIDSeq, 'Navjyot)

So result of select * from StudentData is below

ID Name

1. Pravin
2. Navjyot

Reseed Sequence:

We can restart sequence like we reseed identity column.

ALTER SEQUENCE dbo. StudentIDSeq
RESTART WITH 2;

The above statement will allow you to restart the sequence from 2.

Note: One more point I would like to mention here is Sequence doesn't rollback its value if transaction in which it's used is rollback.

Reuse Sequence value using CYCLE option:

If you have set the cycle option on, your Sequence object will re-use numbers. Let us see this in an example. By stating CYCLE your sequence cycle option will be set to true.

CREATE SEQUENCE [dbo].[StudentIDSeq]
 AS [tinyint]
 START WITH 2
 INCREMENT BY 5
 CYCLE

After 255 sequence will again start with 2.

Performance wise Sequence has advantage over the identity but it has disadvantages too.

Disadvantages:

1. Allows to use NEXT VALUE FOR in a UNION ALL, but not in a UNION, EXCEPT, INTERSECT, or with DISTINCT.
2. NEXT VALUE FOR function is not allowed in check constraints, default objects, computed columns, views, user-defined functions, user-defined aggregates, sub-queries, common table expressions, or derived tables