June 3, 2015 at 9:10 pm
Comments posted to this topic are about the item Default Constraint Script
June 3, 2015 at 10:02 pm
I dont get this issue in SQL 2014 SP1 or SQL 2014 CU6?
It's the same statement generated with or without data.
June 3, 2015 at 11:28 pm
On SQL 2012 I get script B before and after inserting the data.
Microsoft SQL Server 2012 (SP1) - 11.0.3153.0 (X64)
Jul 22 2014 15:26:36
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
I also prefer script A for readability.
June 4, 2015 at 12:03 am
I have tested it in SQL Server 2008R2 / SQL Server 2012 Express and SQL Server 2014 Express.
You are right in SQL Server 2012 it gives 2 times the script B. (also in SQL Server 2008R2)
That is why I was suprised that SQL Server 2014 did it on another way.
I mentioned in the question that I used SQL Server 2014.
So I thought it should be worth to share this issue as a QOTD.
June 4, 2015 at 12:37 am
And by the way I like script A the most.
It is shorter. When you read the table script you see directly if there is a default on a column.
It is then not necessary to look in the script afterwards the table script.
June 4, 2015 at 1:55 am
SQL 2012, I've got Script B and A.
June 4, 2015 at 1:57 am
Is that SQL Server 2012 Express or another version ?
I have tested it in the Express Version
June 4, 2015 at 1:59 am
Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
May 14 2014 18:34:29
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
June 4, 2015 at 2:12 am
I guessed right, as I'd have expected B for both, but that would have made the question pointless so it was a question of which one gave A instead 😉
But if this isn't documented, then presumably it could change at any point, or be different between versions/editions/releases, which could explain people's differing results.
June 4, 2015 at 2:17 am
TjhomasH6610 (6/3/2015)
On SQL 2012 I get script B before and after inserting the data.
Same here.
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
June 4, 2015 at 2:19 am
I used
Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
Dec 28 2012 20:23:12
Copyright (c) Microsoft Corporation
Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
June 4, 2015 at 2:21 am
Express edition not Developper Edition for 2012 but I also used Express for 2014 so that is not the difference.
June 4, 2015 at 2:43 am
Just guessed, got it wrong--not too surprising since apparently the only way to answer correctly would have been to actually run the operation in SQL server (and also, judging from the replies so far, be running the right version).
June 4, 2015 at 4:14 am
I dislike script B intensely (too verbose and splitting one operation into two without wrapping a transaction around them). Pity it's what I've become used to getting (in fact I usually edit anything in this form to be more like script A).
The behaviour of SQL Server isn't really predictable on this stuff, it can change any time as a side effect of a hot fix or a CU or an SP or a new release, and may not be consistent across editions with the same version and update state. It may even change for some other causes too - I don't imagine anyone has done enough testing to be sure that the behaviour in the question is consistent whatever else (other constraints, indexes, CLR Types, Filestream, instore optimisation, for example) is in the table definition or between differently configured instances.
Tom
June 4, 2015 at 5:35 am
Hany Helmy (6/4/2015)
TjhomasH6610 (6/3/2015)
On SQL 2012 I get script B before and after inserting the data.Same here.
I get the same thing.
Viewing 15 posts - 1 through 15 (of 41 total)
You must be logged in to reply to this topic. Login to reply