SEQUENCE in SQL Server 2011

SQL Server 2011 (or Denali) has now arrived CTP. In this article we will look at a core new feature of SQL Server 2011 which is SEQUENCE. 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 believe.

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.

Restarting a Sequence

Can we restart Sequence in SQL Server 2011? Of course you can. In identity you previously used Reseed for this.

ALTER SEQUENCE dbo.EmployeeSeq
RESTART WITH 2;

Above statement will allow you to restart the sequence from 2.

Sequence Transactions

Another issue is, what will happen to the next number of a sequence when the transaction is rolled backed.
Let us check this. First we will see what is the current value of  the sequence.

SELECT current_value FROM sys.sequences
WHERE Name = ‘EmployeeSeq’

Since, we have restarted the sequence this will return a value of 2 and now let us execute this in transaction and later we will rollback the transaction.

BEGIN TRAN
INSERT INTO Employee
(ID,Name)
VALUES
(NEXT VALUE FOR EmployeeSeq, ‘Asanka’)
ROLLBACK TRAN

Again, we will check the next value for the sequence:

SELECT current_value FROM sys.sequences
WHERE Name = ‘EmployeeSeq’

Above query will return 7 which means the rollback statement does not rollback the next value for the sequence. This behaviour is the same as Identity.

CYCLE

 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].[EmployeeSeq]
 AS [tinyint]
 START WITH 2
 INCREMENT BY 5
 CYCLE

 To see this option to work let us execute following statement.

SELECT
NEXT VALUE FOR EmployeeSeq
GO 100

Following image is a part of results you would get and you can see that after 255 it has restarted to 0 again.

OVER

Let us create a new sequence.

CREATE SEQUENCE SeqOrder AS tinyint
                START WITH 1
                INCREMENT BY 1
                MINVALUE 1
                NO MAXVALUE
                CYCLE;
GO

Now, let us run following query:

SELECT ID,Name,
NEXT VALUE FOR SeqOrder OVER (ORDER BY Name DESC) As [Order]
FROM Employee;

The results should be as shown below:



This means you can use Sequence as a running number in a query.
 

Performance of Sequence vs Identity

For me this is the best aspect of using Sequence. Performance wise it has advantage over the identity.
Let’s measure this.
I will create three tables; timing to measure the time, idt to insert data with identity and seq for insert data with sequence.

CREATE TABLE timing(
Instance varchar(50),
occ_time datetime default getdate())
CREATE table idt
(ID int identity(1,1), Des Varchar(100) )
CREATE table seq
(ID int  , Des Varchar(100) )

Then I will create two procs, insert_idt to insert data with identity and insert_seq to insert data with sequence.
Creating insert_idt procedure:

CREATE PROC insert_idt
AS
BEGIN
INSERT INTO idt
(Des)
VALUES (‘insert idt’)
INSERT INTO timing
(Instance)
Values (‘Insert idt’)
END

Creating procedure insert_seq:

CREATE PROC insert_seq
AS
BEGIN
INSERT INTO seq
(ID,Des)
VALUES (NEXT VALUE FOR SEQ5,’insert seq’)
INSERT INTO timing
(Instance)
Values (‘Insert seq’)
END

 Then I executed each proc 10000 times:

exec insert_idt
 GO 10000
 exec insert_seq
 GO 10000

Then we measure the timing for each batch:

SELECT CAST(MAX(occ_time) – MIN(occ_time) AS TIME) FROM timing
 WHERE Instance =’Insert idt’
 SELECT CAST(MAX(occ_time) – MIN(occ_time) AS TIME) FROM timing
 WHERE Instance =’Insert seq’ 

In this test, executing of the first batch of procs (using Identity) took16.557 seconds and second (using Sequence) took 14.33 seconds, thus demonstrating the performance advantage of Sequence. This advantage will be much greater you are testing this in a table where there is a large number of records.

Limitations of Sequence

·         You are allowed to use NEXT VALUE FOR in a UNION ALL, but not in a UNION, EXCEPT, INTERSECT, or with DISTINCT. In which case you will get the following error message.
Msg 11721, Level 15, State 1, Line 3
NEXT VALUE FOR function cannot be used directly in a statement that uses a DISTINCT, UNION (except UNION ALL), EXCEPT or INTERSECT operator.

·         In addition, I attempted to create a view with sequence and failed with following error message.
Msg 11719, Level 15, State 1, Procedure vw_1, Line 4
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.
·         An error will be thrown when the NEXT VALUE FOR function is used in a TOP, OVER, OUTPUT, ON, WHERE, GROUP BY, HAVING, ORDER BY, COMPUTE, or COMPUTE BY clause.
·         Finally an error is thrown if the NEXT VALUE FOR function is used in the WHEN MATCHED clause, the WHEN NOT MATCHED clause, or the WHEN NOT MATCHED BY SOURCE clause of a merge statement.

Final words

Note that these samples are from CTP1 and there may be some syntax and feature changes in the final release but this core feature will exist.

Author: Dinesh Asanka