SQL Server 2012: Adding Not Null columns to an existing table

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
GO
CREATE TABLE tblPlanDiff(Sno int identity,Col_1 int,Col_2 int)
GO
DECLARE @i int
SET @i=1
WHILE(@i<=100000)
BEGIN
BEGIN TRAN
INSERT INTO tblPlanDiff values(@i*2,@i*3)
COMMIT TRAN
SET @i=@i+1  
END

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.

SELECT *
,[Col_3]=0,[Col_4]=''
INTO tblplandiff1 from tblplandiff
-- Create neccessary constraint and indexes. Takes 1169 ms
CREATE UNIQUE CLUSTERED INDEX ix_sno on tblplandiff1(sno)
GO
CREATE NONCLUSTERED INDEX ix_Col1_Col2 on tblplandiff1(Col_1) INCLUDE(Col_2)
GO
ALTER TABLE tblplandiff1 ADD CONSTRAINT DF_COL_3_0 DEFAULT (0) FOR COL_3
GO
ALTER TABLE tblplandiff1 ADD CONSTRAINT DF_COL_4 DEFAULT ('') FOR COL_4
GO
-- Swap Tables
sp_rename 'tblplandiff','tblplandiffOld'
sp_rename 'tblplandiff1','tblplandiff'

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.

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 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

Thanks,

Ahmad Osama

https://twitter.com/ahmad_4u