SQL Server 2012 : Identity VS Sequence: A performance Comparison

This post compares the performance of adding an identity column to a table using Identity property versus by using sequence objects.

The below code creates a table and fills it with demo data.

DROP TABLE tblPlanDiff
GO
CREATE TABLE tblPlanDiff(Col_1 int,Col_2 int)
GO
DECLARE @i int
SET @i=1
WHILE(@i<=200000)
BEGIN
BEGIN TRAN
INSERT INTO tblPlanDiff values(@i*2,@i*3)
COMMIT TRAN
SET @i=@i+1  
END
GO

The below code adds an identity column via the identity property.

Alter table tblPlanDiff add Sno int NOT NULL identity

1_SQL_Server_2012_Identity_VS_Sequence_A_performance_Comparison

As shown in above snapshot, it takes 8 sec to execute.

The below code creates a sequence object and adds a new column with a default property set to use the next value from the sequence object.

-- Create Sequence
CREATE SEQUENCE Identity_Sequence
    AS int
    START WITH 1
    INCREMENT BY 1
GO
-- Add Column with default value
Alter table tblPlanDiff add Sno int NOT NULL Constraint DF_Identity_Col DEFAULT(NEXT VALUE FOR Identity_Sequence)

2_SQL_Server_2012_Identity_VS_Sequence_A_performance_Comparison

As shown above it only takes a sec to add the column which perfectly simulates the behavior of an identity column created above.

Sequence Wins !!!

 
Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

 
Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

One Comment on “SQL Server 2012 : Identity VS Sequence: A performance Comparison”

  1. Can you try inserting 50 k rows in each table rather than UP/Alter and then check performance ? I think Sequence would suck in that case if you are not using cache.

Leave a Reply

Your email address will not be published. Required fields are marked *