December 1, 2015 at 9:27 am
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.
December 1, 2015 at 9:50 am
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
December 2, 2015 at 2:24 am
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!
December 3, 2015 at 8:42 am
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:
December 3, 2015 at 8:56 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:
Crushingly disappointed...!
🙁
December 3, 2015 at 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 improvements
On my defense I'd say you can taste it in Developer Edition too. :-D:-D
December 4, 2015 at 4:39 am
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!
March 24, 2017 at 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 🙂
March 24, 2017 at 10:22 am
sqldoubleg - Thursday, December 3, 2015 10:19 AMGazareth (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
March 24, 2017 at 11:00 am
TomThomson - Friday, March 24, 2017 10:22 AMsqldoubleg - Thursday, December 3, 2015 10:19 AMGazareth (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.
March 25, 2017 at 7:43 pm
djrubin - Friday, March 24, 2017 10:11 AMGood 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
Change is inevitable... Change for the better is not.
March 25, 2017 at 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!
--Jeff Moden
Change is inevitable... Change for the better is not.
March 26, 2017 at 3:31 am
Gazareth - Friday, March 24, 2017 11:00 AMTomThomson - Friday, March 24, 2017 10:22 AMsqldoubleg - Thursday, December 3, 2015 10:19 AMGazareth (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!
March 26, 2017 at 3:33 am
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 AMGood 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!
March 26, 2017 at 3:34 am
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 30 total)
You must be logged in to reply to this topic. Login to reply