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

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)

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