question

Håkan Winther avatar image
Håkan Winther asked

SQL server 2012 - adding column online

If you alter a table and add a column with "NOT NULL" and a default, the operation will be online and the schema lock will be extremely short as the operation will be meta data change and not a data change. When you select from the table, you'll get the default value from the meta data for old records. New or updated records will get the default value stored in the table or the value specified. But where is the meta information stored? If you drop the default constraint, the old records still gets the default value from somewhere. Can anyone spread some light of this behavior? You can try this in the AdventureWorks2008R2 database in SQL server 2012: ALTER TABLE Production.TransactionHistoryArchive ADD ModifiedBy VARCHAR(50) NOT NULL CONSTRAINT DF_TransactionHistoryArchive_ModifiedBy DEFAULT('Håkan Winther'); GO SELECT top 100 * FROM Production.TransactionHistoryArchive ALTER TABLE [Production].[TransactionHistoryArchive] DROP CONSTRAINT [DF_TransactionHistoryArchive_ModifiedBy] END SELECT top 100 * FROM Production.TransactionHistoryArchive;
sql-server-2012ddlonline
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 for a very good question.
2 Likes 2 ·
Is not it a bit confusing "If you drop the default constraint, the old value still get the default value from somewhere"? Can you please elaborate it a bit more. Thanks.
0 Likes 0 ·
Well, it should have been the old records.
0 Likes 0 ·

1 Answer

· Write an Answer
Pavel Pawlowski avatar image
Pavel Pawlowski answered
Hi, here is an interesting blog post related to this problem: [Online non-NULL with values column add in SQL Server 2012][1] [1]: http://rusanu.com/2011/07/13/online-non-null-with-values-column-add-in-sql-server-11/
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 Thank you, this is exactly what I was looking for! It took only 40 minutes to get an excellent answer. (thats the true power of social networking) I got the question on a seminar that I held at Microsoft in Sweden.
0 Likes 0 ·
+1. Nice find really.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.