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 SQL Server Add Not Null Column to Existing Table.  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.

Let’s now add two new columns to the newly created table and observe the profiler activity.

1_SQL_Server_2012_Adding_Not_Null_columns_to_an_existing_table

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.

2_SQL_Server_2012_Adding_Not_Null_columns_to_an_existing_table

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.

3_SQL_Server_2012_Adding_Not_Null_columns_to_an_existing_table

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.

 

Regards

Ahmad Osama

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook