March 31, 2015 at 5:52 am
sipas (3/31/2015)
TomThomson (3/31/2015)
Ed Wagner (3/31/2015)
sipas (3/31/2015)
Stephanie Giovannini (3/30/2015)
Instead of inventing the word "beginnator" as the opposite of terminator, how about "initiator"?In fact you need to use 'beginninginator' if you want to have a chance of taking over the entire tri-state area.:satisfied:
Only if your nemesis is a duck-billed, egg-laying mammal of action. 😛
Given that the phrase "tri-state area" generally applies to some area of the USA surely a nemesis in that connection would be a member of the Blarina family rather than of Ornithorhyncus? 😀
But 'Perry the American short-tailed shrew' is not as snappy.:-)
True. It wouldn't fit into songs that children like as easily, although they have come up with some interesting songs over the years. Sadly, my daughter has pretty-much outgrown the show. I enjoyed watching it with her.
March 31, 2015 at 6:35 am
sipas (3/31/2015)
TomThomson (3/31/2015)
Ed Wagner (3/31/2015)
sipas (3/31/2015)
Stephanie Giovannini (3/30/2015)
Instead of inventing the word "beginnator" as the opposite of terminator, how about "initiator"?In fact you need to use 'beginninginator' if you want to have a chance of taking over the entire tri-state area.:satisfied:
Only if your nemesis is a duck-billed, egg-laying mammal of action. 😛
Given that the phrase "tri-state area" generally applies to some area of the USA surely a nemesis in that connection would be a member of the Blarina family rather than of Ornithorhyncus? 😀
But 'Perry the American short-tailed shrew' is not as snappy.:-)
'Seilagh the Short-tailed Shrew' perhaps? 😛 Or if that will get me into trouble with the girls :unsure: it can be Seán instead of Seilagh. :Whistling:
After all, Perry the Platypus omits DuckBilled, so why shouldn't SeilaghSeán the short-tailed Shrew omit "American" (or, perhaps, omit "Northern" since Blarina Brevicaudis - the species I was thinking of - is the northern short-tailed shrew).
Tom
March 31, 2015 at 7:08 am
Just not crazy about this phrasing:
Since few semicolons are used,
I use semicolons on terminators for all my statements. Everyone should.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 31, 2015 at 7:48 am
Thanks for the question, Steve, it has created a fascinating debate.
Regardless of the merits of adding semicolons at the end of all statements (something which I don't do, but should), why is the WITH statement so much more picky about it than just about any other T-SQL statement? Why don't SELECT/INSERT/UPDATE/DELETE have the same requirements?
March 31, 2015 at 7:54 am
I think it's so that the CTE definition doesn't get mistaken for an index hint or something similar.
John
March 31, 2015 at 7:55 am
stephen.long.1 (3/31/2015)
Regardless of the merits of adding semicolons at the end of all statements (something which I don't do, but should), why is the WITH statement so much more picky about it than just about any other T-SQL statement? Why don't SELECT/INSERT/UPDATE/DELETE have the same requirements?
Because they were here first, at a time when the language was much simpler, and because not terminating statements is still only deprecated, not removed (yet)
It's not WITH, that's just the more noticeable. Several new command need either to be terminated or for the previous statement to be terminated. MERGE and THROW come immediately to mind, there are others.
The WITH keyword is used in lots and lots of places. Table hints, CTEs, DDL statements, etc. So it's ambiguous.
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
March 31, 2015 at 8:00 am
I use semicolons on terminators for all my statements. Everyone should.
+1. We instituted a shop standard to apply this practice in all new code and any older code being altered.
March 31, 2015 at 8:10 am
stephen.long.1 (3/31/2015)
Thanks for the question, Steve, it has created a fascinating debate.Regardless of the merits of adding semicolons at the end of all statements (something which I don't do, but should), why is the WITH statement so much more picky about it than just about any other T-SQL statement? Why don't SELECT/INSERT/UPDATE/DELETE have the same requirements?
They will at some point. At least that's the word from Microsoft. For sure now, it's CTEs and Service Broker statements. I think there's something about it in XML too. MERGE has to end with a terminator. I haven't seen a complete list, but it is growing.
It's coming
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 31, 2015 at 11:00 am
TomThomson (3/30/2015)
sknox (3/30/2015)
ZZartin (3/30/2015)
Let's be honest requiring statement terminators is to make life easier on the developers writing the syntax parser not because there is any inherent value in a ;That is not the case. Statement terminators don't only make the developers' work easier, but the query parser's as well. A parser with extra code to handle situations which would be clearer with a terminator will use extra CPU time and delay query results.
As a very simple example, your statement took a small amount of extra effort on my part to parse properly, because you're missing a terminator in there.
I believe it's been demonstrated (more than 50 years ago) that if two languages are identical except that one uses statement separators and the other uses statement terminators, and they allow compound statements, the one using separators costs less to parse. But if they don't have complex statements the parsing complexity of the two is identical (since they are syntactically identical, differing only in the terminology used to describe them). So the terminator case is never easier to parse.
That's interesting, and I'd love to see your reference for that. Specifically, I'd like to know if that was proven for languages with terms that can perform as different parts of a statement (such as T-SQL's WITH, which can indicate options for an DDL statement or the beginning of a CTE statement.)
I'd also like to know if that proof included languages with neither statement terminators nor statement separators, as that is more akin to the current state of T-SQL.
March 31, 2015 at 11:10 am
Grant Fritchey (3/31/2015)
I use semicolons on terminators for all my statements. Everyone should.
You do or Prompt does it for you?
March 31, 2015 at 11:18 am
Steve Jones - SSC Editor (3/31/2015)
Grant Fritchey (3/31/2015)
I use semicolons on terminators for all my statements. Everyone should.
You do or Prompt does it for you?
Well, Prompt does it for me now. I used to do it on my own, but now, yeah, all automated.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 4, 2015 at 12:31 pm
sknox (3/31/2015)
TomThomson (3/30/2015)
sknox (3/30/2015)
ZZartin (3/30/2015)
Let's be honest requiring statement terminators is to make life easier on the developers writing the syntax parser not because there is any inherent value in a ;That is not the case. Statement terminators don't only make the developers' work easier, but the query parser's as well. A parser with extra code to handle situations which would be clearer with a terminator will use extra CPU time and delay query results.
As a very simple example, your statement took a small amount of extra effort on my part to parse properly, because you're missing a terminator in there.
I believe it's been demonstrated (more than 50 years ago) that if two languages are identical except that one uses statement separators and the other uses statement terminators, and they allow compound statements, the one using separators costs less to parse. But if they don't have complex statements the parsing complexity of the two is identical (since they are syntactically identical, differing only in the terminology used to describe them). So the terminator case is never easier to parse.
That's interesting, and I'd love to see your reference for that. Specifically, I'd like to know if that was proven for languages with terms that can perform as different parts of a statement (such as T-SQL's WITH, which can indicate options for an DDL statement or the beginning of a CTE statement.)
I'd also like to know if that proof included languages with neither statement terminators nor statement separators, as that is more akin to the current state of T-SQL.
I believe it's been demonstrated (more than 50 years ago) that if two languages are identical except that one uses statement separators and the other uses statement terminators, and they allow compound statements, the one using separators costs less to parse. But if they don't have complex statements the parsing complexity of the two is identical (since they are syntactically identical, differing only in the terminology used to describe them). So the terminator case is never easier to parse.[/quote]
It was a conference paper some time in the 60s, can't be more accurate than that - I saw it in autumn or early winter of 1967 which means there are only an unreasonable number of conferences to look at (in the unlikely even that the proceedings are still available anywhere accessible) rather than a completely impossible number. So no, I can't give you the reference.
But it probably doesn't matter - after a bit of hard brain strain I remembered the argument. There isn't an issue about languages without compound statements - it's trivially true that statement terminators and statement separators are equivalent in those languages. But for languages with compound statements, I don't actually believe it as I stated it - I suspect there were qualifications of the assertion in the original paper that I had forgoten. I imagine that there was a requirement that the syntax was such that the compound statement end marker was always lexically separable from the preceding component statement even if there was no separator/terminator in front of it, but there may have been some other effectively equivalent restriction. Of course in the case where the end marker isn't separable like that, it's easy enough to invent an equivalent language where it is - and it remains true that where it is lexically separable the separator language is cheaper to parse, while in the case where it isn't either the separator language is cheaper to parse (where the end marker is separable from some statements but not from others, and this is exploutabble - it won't always bee exploitable) or costs the same (where the end marker is never separable on ts own).
It remains true that a language with separators is often cheaper than a language with terminators, and is never more expensive (and many languages are cheaper in a separator version than in a terminator version). And SQL is one of the cases where ";" as a separator would often be cheaper to parse than ";" as a terminator and never be nmore expensive.
The argument for SQL goes like this: any time the end of a compound statement can be detected independently of a previous statement end marker by the compound statement end marker on its own in a language using terminators, then the terminator preceding it is redundant; so it can be discarded and and the actions it invokes can be invoked instead by the compound statement terminator; but if there are nested compound statements, and the end marker isn't always seperable, the complexity of handling this has to be dealt with - so only if it is always separable can we guarantee that it's cheaper to use separators.
In the SQL case, we have to distinguish for example XEND (which could be a column name, or an alias) from X;END (which couldn't) = so compount statement end markers aren't all always separable (in fact none of them are always separable). That is preserved if we replace ";" without adjacent space by " " in the alternative language that is to be compared, and it is generally believed that most statement terminators will be adjacent to whitespace (often newline). We don't have to worry about ";WITH" (or of course anything with whitespace between ";" and "WITH") because the ";" is still there even if it is a statement separator. So switching from terminator to separator will sometimes reduce parsing costs and sometimes leave them the same (and I suspect most people would be in favour of using white space adjacent to statement terminators, so if there are compound statements parsing will be cheaper).
Tom
April 4, 2015 at 8:27 pm
Grant Fritchey (3/31/2015)
Steve Jones - SSC Editor (3/31/2015)
Grant Fritchey (3/31/2015)
I use semicolons on terminators for all my statements. Everyone should.
You do or Prompt does it for you?
Well, Prompt does it for me now. I used to do it on my own, but now, yeah, all automated.
Did I miss this setting in Prompt?
I hardly ever used it in the past, but trying to fix that.
April 5, 2015 at 4:53 am
xsevensinzx (4/4/2015)
Grant Fritchey (3/31/2015)
Steve Jones - SSC Editor (3/31/2015)
Grant Fritchey (3/31/2015)
I use semicolons on terminators for all my statements. Everyone should.
You do or Prompt does it for you?
Well, Prompt does it for me now. I used to do it on my own, but now, yeah, all automated.
Did I miss this setting in Prompt?
I hardly ever used it in the past, but trying to fix that.
Version 6 added that in. 6.5 added even more.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 5, 2015 at 11:04 am
Grant Fritchey (3/31/2015)
Steve Jones - SSC Editor (3/31/2015)
Grant Fritchey (3/31/2015)
I use semicolons on terminators for all my statements. Everyone should.
You do or Prompt does it for you?
Well, Prompt does it for me now. I used to do it on my own, but now, yeah, all automated.
Personally, I add them as I'm coding. When I'm cleaning up or rewriting someone else's code (or my old code), I'll start with a format, insert ;, remove [], but from there on I'll write it 'correctly'
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 15 posts - 46 through 60 (of 64 total)
You must be logged in to reply to this topic. Login to reply