posted 8/14/2012 8:21:36 PM by Ahmad Osama - Views: [4385]
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 !!!
If you like our blogs do like us on Facebook at http://www.FaceBook.com/SQLServerGeeks
Thanks,
Ahmad (@ahmad_4u)
Ahmad Osama (Member since: 1/25/2012 1:03:07 PM) Ahmad Osama is a MCP Database Administrator/Developer, an avid gamer and a chicken lover. Ahmad started his career in the sales industry working as database executive; responsible for report writing, application development and basic database administration. In 2008 he joined World Fashion Exchange as Database Administrator. While in this role he focused on troubleshooting and performance tuning. In 2010 he joined The Perfect Future Technologies and has been awarded as best new comer and expert of the year working as database administrator on one of largest political database in the world. Ahmad has experience in database administration, SSIS, performance tuning, VLDBs and web development. When not working on SQL Server, he can be found glued to his Xbox.
View Ahmad Osama 's profile
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 comment