lundi 2 février 2015

Default value stays after I dropped the default constraint


So, here is a test for fun on SQL SERVER 2012. I have a test table, you could choose any table you have in your local.


Then I add a NOT NULL column with default constraint:



alter table test add new_col8 nvarchar(25) not null constraint df_new_col8 default('aaa');


It won't be surprised to see the value get populated but the physical size is still 0. You could refer to the scripts in Why does ALTER COLUMN to NOT NULL cause massive log file growth?.


I used following script and you need to change it a bit when you tested it.



DBCC IND ('master', 'test', 0);

DBCC TRACEON (3604);
GO
DBCC PAGE ('master', 1, 692, 3);
GO


Now, I decided to drop the default constraint. I checked the table again, but the value stays. I would expect the values go back to NULL as physical size is still 0.



alter table test drop constraint df_new_col8;


I checked the sys.default_constraints table and sys.columns table, there is no clue for this default value. How does SQL SERVER still get the default value?!





Aucun commentaire:

Enregistrer un commentaire