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

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 *