kosha5 (12/1/2015)
It's interesting but tricky. For SQL 2012. What if we drop Default constraint afterwards? It should not "delete" existing data, right? I mean since SQL server started substitution of data from Default value for empty column, it should preserve this effect even after Default constraint removal. Otherwise "data" will be cleared, which is not right, it's not expected behaviour. We should not loose any data just dropping constraint. does it mean SQL physically populates data into pages when we drop Default constraint?In other words:
1. Create new not null column with Default.
2. Select - "data" is there.
3. Drop default constraint.
4. Select - data must be there just the way it was up to SQL 2008.
In one of these steps the actual physical work needs to be done.
When the constraint is dropped, the Storage Engine metadata around the default value remains, which is why those rows added while the constraint was there keep their values. If you then add back a default constraint for that same column, subsequent inserts that don't specify a value for that column will actually populate the physical column value - as there's no way for the Storage Engine to know when the row was inserted and which default value to use, it must actually populate the column value.
Hope this helps.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005