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




Array

No comments yet... Be the first to leave a reply!