SEQUENCE in SQL Server 2012

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 systax and feature changes in the final release but this core feature will exist.

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 |