question

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;