Adding Not Null Column to Existing Table in SQL Server 2012

This blog is the continuation of my last blog link is here;

where in I wrote about a new feature in SQL Server 2012 which optimizes addition of a adding not null column to existing table in SQL Server. This one explains how this feature is being implemented in SQL Server 2012.

The explanation is in the DMV “sys.system_internals_partition_columns”. I came across this undocumented DMV when going through a blog. It keeps track of several column metadata. I also found that SQL Server 2012 added to new columns – has_default and default_value to this DMV. Thus, when a column with a default value is added to a table, the has_Default column is set to 1 and the default_Value column is set to the default value of the added column.  Let’s demo the concept to make it more concrete.

The below script creates a table and then adds a new column with a default value.

DROP TABLE tblPlanDiff
GO
CREATE TABLE tblPlanDiff(Sno int identity,Col_1 int,Col_2 int)
GO
ALTER TABLE tblplandiff ADD Col_3 int NOT NULL DEFAULT(100)
Let’s now query sys.system_internals_partition_columns and check out the values of the new columns.

1_SQL_Server_2012_Adding_Not_Null_columns_to_an_existing_table_Explained

The above snapshot shows that the Col_3 has a default value of 100. So, when a new column with a default value is added to a table in SQL Server 2012, instead of updating all rows

with the default value as in versions prior to SQL Server 2012, only the table metadata is changed. This optimizes the column addition which I showed in my last blog.

That’s it for this blog… keep checking this space for more SQL Server knowledge…

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

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Leave a Reply

Your email address will not be published. Required fields are marked *