Paul Randal (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.
That was very clarifying!
For those curious to see what metadata Paul is talking about, I did some research yesterday and wrote this query (to be executed in DAC)
-- Here you drop the constraint first
-- And now see how the column's hidden metadata still knows there is a default value even the constraint does not exist anymore
SELECT OBJECT_NAME(p.[object_id]) AS table_name
, c.name AS column_name
, v.value AS defult_value
FROM sys.sysrscols AS rsc
INNER JOIN sys.sysseobjvalues AS v
ON v.id = rsc.rsid
AND v.subid = rsc.rscolid
INNER JOIN sys.partitions p
ON rsc.[rsid] = p.[partition_id]
INNER JOIN sys.columns AS c
ON c.object_id = p.[object_id]
AND c.column_id = rsc.rscolid
WHERE p.[object_id] = OBJECT_ID ('MissingDefaultValues');
Thanks all for commenting and sharing!