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.
]]>