June 4, 2015 at 9:39 am
Robert Eder (6/4/2015)
The problem with script A is the constraint name will be system generated. Script B is also written to use a system generated name. It is best practice to use Script B, but provide an name. The system generated name is unique to each instance of the database. If you ever need to write a script to update the default constraint with a system generated name, it is painful. however, if you name it, it is easy.
True. However, the original table create script did not name the constraint it was just inline. I agree that in the real world I would name my constraint but that wasn't the point of this question. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 4, 2015 at 9:50 am
Sean Lange (6/4/2015)
Robert Eder (6/4/2015)
The problem with script A is the constraint name will be system generated. Script B is also written to use a system generated name. It is best practice to use Script B, but provide an name. The system generated name is unique to each instance of the database. If you ever need to write a script to update the default constraint with a system generated name, it is painful. however, if you name it, it is easy.True. However, the original table create script did not name the constraint it was just inline. I agree that in the real world I would name my constraint but that wasn't the point of this question. 😉
Sure. I was replying to another post that stated that he prefers script A o readability. I was only providing some insight of the consequences.
June 4, 2015 at 10:08 am
Robert Eder (6/4/2015)
Sean Lange (6/4/2015)
Robert Eder (6/4/2015)
The problem with script A is the constraint name will be system generated. Script B is also written to use a system generated name. It is best practice to use Script B, but provide an name. The system generated name is unique to each instance of the database. If you ever need to write a script to update the default constraint with a system generated name, it is painful. however, if you name it, it is easy.True. However, the original table create script did not name the constraint it was just inline. I agree that in the real world I would name my constraint but that wasn't the point of this question. 😉
Sure. I was replying to another post that stated that he prefers script A o readability. I was only providing some insight of the consequences.
For the record, script A also allows one to name the constraints.
Also, on the difference - if you see scripting as a way to be able to reconstruct objects, then the two are actually equal. However, if you use scripting to check for changes, then this kind of change is very annoying. I had it wrong because I always see the script B form, but I must admit that I have hardly ever used SQL 2014 so far.
June 4, 2015 at 11:24 am
I got B and then A using 2012 (Microsoft SQL Server 2012 - 11.0.5548.0 (X64)). I would have expected version B for both as well though. Odd behavior.
June 4, 2015 at 10:31 pm
Not really a fan of this particular question.
Different patch levels - especially in 2014 - can cause different results. SSMS is extremely buggy in 2014.
On the other hand, SQL Server 2016 CTP2 produces the same script result (script A) for both scripts. In addition, SSMS is a lot more stable in 2016. At least so far for me. 2014 crashed daily and I have yet to see SSMS 2016 crash.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 5, 2015 at 1:04 am
Thanks for the question.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 5, 2015 at 1:04 am
SQLRNNR (6/4/2015)
Not really a fan of this particular question.Different patch levels - especially in 2014 - can cause different results. SSMS is extremely buggy in 2014.
On the other hand, SQL Server 2016 CTP2 produces the same script result (script A) for both scripts. In addition, SSMS is a lot more stable in 2016. At least so far for me. 2014 crashed daily and I have yet to see SSMS 2016 crash.
What do you do with SSMS? I've never had SSMS 2014 crash on me.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 5, 2015 at 8:32 am
Koen Verbeeck (6/5/2015)
SQLRNNR (6/4/2015)
Not really a fan of this particular question.Different patch levels - especially in 2014 - can cause different results. SSMS is extremely buggy in 2014.
On the other hand, SQL Server 2016 CTP2 produces the same script result (script A) for both scripts. In addition, SSMS is a lot more stable in 2016. At least so far for me. 2014 crashed daily and I have yet to see SSMS 2016 crash.
What do you do with SSMS? I've never had SSMS 2014 crash on me.
Write TSQL. It is a very common problem that SSMS 2014 crashes regularly.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 5, 2015 at 8:34 am
I've been using SSMS2014 for about a year. The only time I've ever had a crash is if the connection has been closed (usually because I've left it unattended overnight) and attempt to close SSMS without reconnecting. Otherwise it's no less stable than any previous version IME.
June 5, 2015 at 9:12 pm
Robert Eder (6/4/2015)
Sean Lange (6/4/2015)
Robert Eder (6/4/2015)
The problem with script A is the constraint name will be system generated. Script B is also written to use a system generated name. It is best practice to use Script B, but provide an name. The system generated name is unique to each instance of the database. If you ever need to write a script to update the default constraint with a system generated name, it is painful. however, if you name it, it is easy.True. However, the original table create script did not name the constraint it was just inline. I agree that in the real world I would name my constraint but that wasn't the point of this question. 😉
Sure. I was replying to another post that stated that he prefers script A o readability. I was only providing some insight of the consequences.
Actually I would alter script B to name the constraint if I wanted it named (which I probably would) - as Higo has already pointed out, that is easy to do. It still makes a single operation instead of a split one so it reduces the things which can go wrong as well as being more concise and easier to read. Here's what it looks like:-
CREATE TABLE [dbo].[TestDefault](
[aIntWithDefault] [int] NOT NULL constraint [ThisIsMyName] DEFAULT (0),
[aInt] [int] NOT NULL
) ON [PRIMARY]
GO
Tom
June 8, 2015 at 12:07 am
I understand that your script is the most clear Tom.
The default is on the same line as the column name and the constraint has a self given name.
But that was not my point I would pay attention to.
It was the automatically created scripts which differs.
June 8, 2015 at 7:01 am
Thanks for the question.
Viewing 12 posts - 31 through 41 (of 41 total)
You must be logged in to reply to this topic. Login to reply