The Mysterious Case of the Missing Default Value

  • I hadn't realised 2012 did this. One small adjustment to your original solution however, add the column first then add the default as a separate operation. This won't change any existing rows in the table and so it is fast, then update in batches if required.

  • kosha5 (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.

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • Paul Randal (12/1/2015)


    kosha5 (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!

  • Nice article, cheers. Might be worth making the point clear that it's an Enterprise Edition feature, so readers can avoid the crushing disappointment I experienced when trialing it on Standard :crying:

  • Gazareth (12/3/2015)


    Nice article, cheers. Might be worth making the point clear that it's an Enterprise Edition feature, so readers can avoid the crushing disappointment I experienced when trialing it on Standard :crying:

    Crushingly disappointed...!

    🙁

  • Gazareth (12/3/2015)


    Nice article, cheers. Might be worth making the point clear that it's an Enterprise Edition feature, so readers can avoid the crushing disappointment I experienced when trialing it on Standard :crying:

    Cheers Gaz! You're right, Enterprise Edition not only gets all marketing features but also all engine improvements

    On my defense I'd say you can taste it in Developer Edition too. :-D:-D

  • sqldoubleg (12/3/2015)


    Gazareth (12/3/2015)


    Nice article, cheers. Might be worth making the point clear that it's an Enterprise Edition feature, so readers can avoid the crushing disappointment I experienced when trialing it on Standard :crying:

    Cheers Gaz! You're right, Enterprise Edition not only gets all marketing features but also all engine improvements

    On my defense I'd say you can taste it in Developer Edition too. :-D:-D

    No worries, I'm just bitter because I can't use this to add a field to a large table I have 🙂

    MS don't exactly make it clear either!

  • Good stuff but why not go old school and just add a one-to-one table that has the extra column. App folks don't make all the rules. Once a table has that many records, adding columns always puts me in the mindset of adding tables to avoid just this issue. App selections utilize a view to "see" the whole "table" while their code needs to be modified anyway for the new data, so make them update/insert this additional table with PK/FK. 

    Just my two cents 🙂

  • sqldoubleg - Thursday, December 3, 2015 10:19 AM

    Gazareth (12/3/2015)


    Nice article, cheers. Might be worth making the point clear that it's an Enterprise Edition feature, so readers can avoid the crushing disappointment I experienced when trialing it on Standard :crying:

    Cheers Gaz! You're right, Enterprise Edition not only gets all marketing features but also all engine improvementsOn my defense I'd say you can taste it in Developer Edition too. :-D:-D

    Surely it's in standard edition in SQl Server 2016?

    Tom

  • TomThomson - Friday, March 24, 2017 10:22 AM

    sqldoubleg - Thursday, December 3, 2015 10:19 AM

    Gazareth (12/3/2015)


    Nice article, cheers. Might be worth making the point clear that it's an Enterprise Edition feature, so readers can avoid the crushing disappointment I experienced when trialing it on Standard :crying:

    Cheers Gaz! You're right, Enterprise Edition not only gets all marketing features but also all engine improvementsOn my defense I'd say you can taste it in Developer Edition too. :-D:-D

    Surely it's in standard edition in SQl Server 2016?

    Think I tested it on 2016 SP1 with all the newly available features in Standard, but it seems this wasn't one of them unfortunately. I'll see if I can dig up my results to confirm.

    NB: this article was first published in 2015, today is a re-post.

    Update: Yep, still an Enterprise (& Developer)-only feature, even in SQL 2016 SP1.

  • djrubin - Friday, March 24, 2017 10:11 AM

    Good stuff but why not go old school and just add a one-to-one table that has the extra column. App folks don't make all the rules. Once a table has that many records, adding columns always puts me in the mindset of adding tables to avoid just this issue. App selections utilize a view to "see" the whole "table" while their code needs to be modified anyway for the new data, so make them update/insert this additional table with PK/FK. 

    Just my two cents 🙂

    That's a perfect example of why I never use "tbl" to prefix the name of tables.  It's mighty confusing to folks when you have to change the object name to a view to accommodate something like that and you end up with a view that has a "tbl" prefix in it. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • @Raul,

    Didn't see this article when it was first published.  Job well done!  Thanks for taking the time to write it up!

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Gazareth - Friday, March 24, 2017 11:00 AM

    TomThomson - Friday, March 24, 2017 10:22 AM

    sqldoubleg - Thursday, December 3, 2015 10:19 AM

    Gazareth (12/3/2015)


    Nice article, cheers. Might be worth making the point clear that it's an Enterprise Edition feature, so readers can avoid the crushing disappointment I experienced when trialing it on Standard :crying:

    Cheers Gaz! You're right, Enterprise Edition not only gets all marketing features but also all engine improvementsOn my defense I'd say you can taste it in Developer Edition too. :-D:-D

    Surely it's in standard edition in SQl Server 2016?

    Think I tested it on 2016 SP1 with all the newly available features in Standard, but it seems this wasn't one of them unfortunately. I'll see if I can dig up my results to confirm.

    NB: this article was first published in 2015, today is a re-post.

    Update: Yep, still an Enterprise (& Developer)-only feature, even in SQL 2016 SP1.

    SQL 2016 SP1 has brought to standard development features (in-memory, always encrypted, and so on) but the performance features like online index rebuild and this, are (and I guess always be) enterprise.

    Cheers!

  • Jeff Moden - Saturday, March 25, 2017 7:44 PM

    @Raul,

    Didn't see this article when it was first published.  Job well done!  Thanks for taking the time to write it up!

    djrubin - Friday, March 24, 2017 10:11 AM

    Good stuff but why not go old school and just add a one-to-one table that has the extra column. App folks don't make all the rules. Once a table has that many records, adding columns always puts me in the mindset of adding tables to avoid just this issue. App selections utilize a view to "see" the whole "table" while their code needs to be modified anyway for the new data, so make them update/insert this additional table with PK/FK. 

    Just my two cents 🙂

    Application code doesn't need to be changed if you use 'instead of' triggers for DML operations on the view 🙂

    Cheers!

  • Jeff Moden - Saturday, March 25, 2017 7:44 PM

    @Raul,

    Didn't see this article when it was first published.  Job well done!  Thanks for taking the time to write it up!

    Thanks Jeff, much appreciated!

Viewing 15 posts - 16 through 30 (of 31 total)

You must be logged in to reply to this topic. Login to reply