December 2, 2015 at 9:05 am
I wonder what Alice B Toklas <edit> and Gertrude Stein would have to say about the semicolon?
--Quote me
December 2, 2015 at 9:05 am
xsevensinzx (12/2/2015)
I'm saying the one statement where not having a properly terminated statement above it could cause the code to error as a bug, could benefit from always ensuring it starts with a semicolon where appropriate.
But it's not one statement. There's multiple statements in 2014 which require the previous statement to be terminated, and there are likely to be more in the next version and more in each version after that until it's required for all statements (which MS has said will happen).
In 2014, there's at least
CTEs
THROW
ENABLE/DISABLE TRIGGER
and that's just off the top of my head
I strongly disagree that having a semicolon at the front of a CTE statement is going to cause mass confusion and chaos in reading the rest of the code and promoting horrid bad practices.
You're welcome to disagree, but I've had a couple of cases where, when I asked why a developer had put semicolons in strange places (in one case before most statements, in the other on empty lines between statements) they said that they didn't know where the semicolons had to go because sometimes they're at the beginning of statements and sometimes at the end and there's no pattern (probably based on code on stack overflow or similar).
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 2, 2015 at 9:17 am
xsevensinzx (12/2/2015)
Can't stress semi-colons enough. I didn't have it in my sample, but it should be at the end of each statement. Don't forget to put it at the beginning of each CTE too.
NEVER!!!
Semicolons are TERMINATORS not BEGININATORS!! If every statement is properly terminated there is NO NEED to use the semicolon at the beginning of a CTE crutch!
Microsoft documentation in Books on Line is WRONG to show that when the documentation specifically states that the PRECEDING statement must be terminated with a semicolon.
December 2, 2015 at 9:25 am
GilaMonster (12/2/2015)
xsevensinzx (12/2/2015)
I'm saying the one statement where not having a properly terminated statement above it could cause the code to error as a bug, could benefit from always ensuring it starts with a semicolon where appropriate.But it's not one statement. There's multiple statements in 2014 which require the previous statement to be terminated, and there are likely to be more in the next version and more in each version after that until it's required for all statements (which MS has said will happen).
In 2014, there's at least
CTEs
THROW
ENABLE/DISABLE TRIGGER
and that's just off the top of my head
I strongly disagree that having a semicolon at the front of a CTE statement is going to cause mass confusion and chaos in reading the rest of the code and promoting horrid bad practices.
You're welcome to disagree, but I've had a couple of cases where, when I asked why a developer had put semicolons in strange places (in one case before most statements, in the other on empty lines between statements) they said that they didn't know where the semicolons had to go because sometimes they're at the beginning of statements and sometimes at the end and there's no pattern (probably based on code on stack overflow or similar).
I have placed a semicolon on its own line before, but that is usually while working on the code and I keep adding and deleting code. Once the code is correct for the query I move the semicolon to the end of the statement where it belongs. Have I forgotten on occasion, yes, but I usually find it later and correct it.
December 2, 2015 at 9:25 am
My vote is you write this:
with mycte (col)
as
(select ...
);
If you get a syntax error because:
select mycol from mytable
with mycte (col)
as
(select ...
);
then you do this:
select mycol from mytable;
with mycte (col)
as
(select ...
);
I think the issue with non-terminated statements should just be corrected when it's caught. Not by adding ;WITH as a default. This isn't something that should be causing issues in production. If you write code, terminate the previous statement when it throws an error in development.
December 2, 2015 at 9:51 am
If you know that ; is a terminator and then deliberately use it as an initiator, it is an indication that you are not interested in getting the little details correct.
That's ok, but in my opinion, people who write code need to exhibit this quality.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 2, 2015 at 9:55 am
I terminate all my SQL statements with a semicolon because, apparently, you are supposed to. If anyone is interested in an intellectually stimulating article on the subject see Itzik Ben-Gan's article titled, Semicolon.
-- Itzik Ben-Gan 2001
December 2, 2015 at 11:17 am
GilaMonster (12/1/2015)
polkadot (12/1/2015)
Seriously, does any team really have discussions about these things?Yes, good teams do, because they know that making time to establish standards and practices saves time later on.
If the team doesn't agree on formatting standards and acceptable layouts, then everyone's going to code their own way and the team members will be wasting time changing formats again and again and struggling to read code, and so they'll be busier than they need to be.
It's the old "I don't have time to sharpen the axe, I have too much wood that needs chopping".
Awesome Gail, thank you: that is hand's down the best version of the aphorism "never time to do it right, always time to do it twice" I've ever heard.
Of course, in New Hampshire it'd likely be cutting with a chain saw, not chopping with an ax, but the concept still applies. 😀
Rich
December 2, 2015 at 1:27 pm
Rich Mechaber (12/2/2015)
GilaMonster (12/1/2015)
Awesome Gail, thank you: that is hand's down the best version of the aphorism "never time to do it right, always time to do it twice" I've ever heard.Of course, in New Hampshire it'd likely be cutting with a chain saw, not chopping with an ax, but the concept still applies. 😀
It's old and it's not mine. Can't remember where I read it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 46 through 53 (of 53 total)
You must be logged in to reply to this topic. Login to reply