I have a very big table and added a new NOT NULL column with DEFAULT on it. Not an unusual thing in DWH environment. It is an online operation, it works very quickly, and stores default in a table metadata, as we can learn there. It works perfectly, SELECTs return a default value. After some time I decided to change the default to a different value. And it looks like we can't achieve it by changing the default. I mean, we can drop old default and create new one, but all SELECTSs would show old default value. I'd try to avoid massive UPDATEs because they are long and painful. Is it possible?
--DROP TABLE #t CREATE TABLE #t ( c1 int not null ) INSERT INTO #t VALUES (1) ALTER TABLE #t ADD c2 int not null CONSTRAINT t#c2def default -1 ALTER TABLE #t DROP CONSTRAINT t#c2def SELECT * FROM #t -- c2 = -1 ALTER TABLE #t ADD CONSTRAINT t#c2def DEFAULT -2 FOR c2 SELECT * FROM #t -- still c2 = -1
And more generally, do we have more details on how this feature works? I'd expect that existing non-updated pages should show a new default. That's how I understand 'the default value is stored only in the metadata of the table and the value is looked up, as needed'. Unfortunately, it doesn't work like this. All rows show old default value. I'm checking it on SQL Server 2014.