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 PAGE ('master', 1, 692, 3); 

Now, I decided to drop the default constraint. I checked the table again, but the value stays but the 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?!

  • 1
    Why do you expect that dropping a constraint will update the table's existing values? That would be a bug, if it happened. Feb 2, 2015 at 8:30
  • As for the physical size that doesn't grow, see this question: Table size is not changed after new column is added Feb 2, 2015 at 8:32
  • 1
    And, to follow-up to @ypercube, it would also be a bug if the values of the column went back to null! Feb 2, 2015 at 8:37
  • 1
    So, the table is working as expected. Existing rows keep their values. New rows need values to be inserted. Where is the problem? Feb 2, 2015 at 8:58
  • 3
    The answer to where the values are "stored" is in the blog post by Remus Rusanu in the section "Default value vs. Default constraint". The default value is in there even if the constraint is dropped and the default value from that view is removed if the table is rebuilt even if the default constraint is not removed. Feb 2, 2015 at 9:24


Browse other questions tagged or ask your own question.