0

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 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?!

Community
  • 1
zhongxiao37
  • 143
  • 6
  • 1
    Why do you expect that dropping a constraint will update the table's existing values? That would be a bug, if it happened. – ypercubeᵀᴹ Feb 02 '15 at 08:30
  • As for the physical size that doesn't grow, see this question: Table size is not changed after new column is added – ypercubeᵀᴹ Feb 02 '15 at 08:32
  • 1
    And, to follow-up to @ypercube, it would also be a bug if the values of the column went back to null! – Colin 't Hart Feb 02 '15 at 08:37
  • I agree it will be a bug if it changes back to NULL. But why does the SQL SERVER still keeps the default value? I would guess the default value is still saved in other places and not changed by dropping the default constraint. – zhongxiao37 Feb 02 '15 at 08:42
  • If you insert a new row - after you have dropped the constraint- does it get that (old) default value? I expect not. – ypercubeᵀᴹ Feb 02 '15 at 08:46
  • You could not insert a new row as the column is NOT NULL. You have to specific the value. I also tried using DEFAULT but it won't help. – zhongxiao37 Feb 02 '15 at 08:50
  • 1
    So, the table is working as expected. Existing rows keep their values. New rows need values to be inserted. Where is the problem? – ypercubeᵀᴹ Feb 02 '15 at 08:58
  • The physical size is 0 but value stays. I would expect it's saved somewhere. I could not find it in sys.columns and sys.default_constraints. Where does the default values come from? – zhongxiao37 Feb 02 '15 at 09:08
  • Did you read the answer in the above linked question? – ypercubeᵀᴹ Feb 02 '15 at 09:14
  • 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. – Mikael Eriksson Feb 02 '15 at 09:24

0 Answers0