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