SEQUENCE in SQL Server 2012

Southhill

SQL Server 2012 (or Denali) has now arrived CTP. In thisarticle 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 thisfeature 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 substitutefor the Identity of columns.
Using the identity attribute for a column, you can easilygenerate auto-incrementing numbers (which as often used as a primary key). WithSequence, it will be a different object which you can attach to a table columnwhile inserting. Unlike identity, the next number for the column value will be retrievedfrom memory rather than from the disk – this makes Sequence significantlyfaster 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 sequencenode.

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

Since Sequence is a database object, it needs to be assignedto 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 thevalues 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 wecreated.
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 objectfor the insert.

Restarting a Sequence
Can we restart Sequence in SQL Server 2012? Of course you can. In identity youpreviously used Reseed for this.
ALTER SEQUENCE dbo.EmployeeSeq
RESTART WITH 2;
Above statement will allow you to restart the sequence from2.

Sequence Transactions
Another issue is, what will happen to the nextnumber of a sequence when the transaction is rolled backed.
Let us check this. First we will see what is the currentvalue 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 andnow let us execute this in transaction and later we will rollback thetransaction.
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 statementdoes 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 cycleoption 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 followingstatement.
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 aquery.

Performance of Sequence vs Identity
For me this is the best aspect of using Sequence. Performancewise it has advantage over the identity.
Let’s measure this.

I will create three tables; timing to measure the time, idtto 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 withidentity 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 ina UNION, EXCEPT, INTERSECT, or with DISTINCT. In which case you will get the following errormessage.
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 followingerror message.
Msg 11719, Level15, 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 clauseof a merge statement.

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

By Dinesh Asanka
http://www.sql-server-performance.com/2011/sequence-sql-server-2011/3/