7/27/2012 6:58:07 AM
Ahmad Osama -
Most of you must have come across the pain of adding a not null column with a default value to an existing big table. It takes minutes to add columns. I recently found out that this problem has been resolved in SQL Server 2012. Let’s look into some ways to resolve this in versions prior to SQL Server 2012 and also, let’s see how SQL server 2012 speeds it up.
I have populated a temp table with demo data using the below script in SQL Server 2005.
DROP TABLE tblPlanDiff
CREATE TABLE tblPlanDiff(Sno int identity,Col_1 int,Col_2 int)
DECLARE @i int
INSERT INTO tblPlanDiff values(@i*2,@i*3)
Let’s now add two new columns to the newly created table and observe the profiler activity.
ALTER table tblplandiff add Col_3 int NOT NULL default(0)
ALTER table tblplandiff add Col_4 char(100) NOT NULL default('')
The above profiler snapshot shows that the SQL Server runs an update statement to set the column values as specified by the default constraint. An update is a fully logged statement and thus takes 19 seconds to update 100000 rows. This gets worst with increase in data.
The easiest way to optimize this is to bulk insert the data into a new table along with the default values of the new columns and then adds necessary indexes, constraints, keys etc.
Let’s apply the above approach and compare the time difference.
INTO tblplandiff1 from tblplandiff
-- Create neccessary constraint and indexes. Takes 1169 ms
CREATE UNIQUE CLUSTERED INDEX ix_sno on tblplandiff1(sno)
CREATE NONCLUSTERED INDEX ix_Col1_Col2 on tblplandiff1(Col_1) INCLUDE(Col_2)
ALTER TABLE tblplandiff1 ADD CONSTRAINT DF_COL_3_0 DEFAULT (0) FOR COL_3
ALTER TABLE tblplandiff1 ADD CONSTRAINT DF_COL_4 DEFAULT ('') FOR COL_4
-- Swap Tables
The above profiler snapshot shows that it took only 1.5 seconds to add the new columns compared to 19 seconds with the traditional straight forward approach.
You will notice that I have even created two additional indexes just to get close to a real scenario where in a table might contain indexes and keys.
With that being said, let’s look how the traditional straight forward approach is improved in SQL Server 2012. Let’s run the traditional add query and record profiler activity.
The above profiler snapshot misses the update statements when compared with that of the previous SQL Server 2005 snapshot. Thus, it takes only 353 milli seconds to add the columns, way fast than SQL Server 2005.
That’s it for this blog; in the next one I’ll try to put details of how SQL Server 2012 avoids the update statement.
If you liked this post, do like us on FaceBook at http://www.FaceBook.com/SQLServerGeeks
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
This is very much informative, thanks for this post.
Leave a comment