January 14, 2009 at 12:21 pm
I subscribe to Sql Server magazine. I've noticed that every articl that Itzec -Bengan writes, he uses semi colons at the end of some of his lines when coding. Does anyone know why he does this and\or when to do it or not?
January 14, 2009 at 12:39 pm
unfortunately - that's a change that is slated to be introduced into some "Future version of SQL Server". As in - at some point, those will be REQUIRED as statement separators.
Don't shoot the messenger. I am averse to the idea. 🙂
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 14, 2009 at 12:42 pm
It's an "end of command" marker. Currently, it's voluntary, but as Matt mentioned, it's supposed to be mandatory at some point.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 14, 2009 at 12:44 pm
It does look cool. Very Oraclesque.... 😛
;
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
January 14, 2009 at 12:50 pm
Yes it is a bit weird. In SQL 2005 you have the mandatory requirement to enter a semi-colon at the end of the statement that immediately precedes the use of a CTE (Common Type Expression).
I guess since you can end every T-SQL statement with a semi-colon and not incur a penalty, it saves errors when they are needed.
The other reason might be to do with security. In SQL injection attacks additional conditions can be added to the end of an existing unterminated statement on the server to modify the query. If the query is now terminated with a semi-colon then this variant of query modification is prevented but obviously does not address the attacker adding additional statements terminated with a semi-colon.
Si
January 14, 2009 at 1:13 pm
I believe it is required in the latest SQL standard. (correct me if I'm wrong, I haven't actually read the standards docs)
One of the reasons that it will be required in future versions is because the T-SQL language is getting more and more complex, and it's getting harder to write the parser so it can always figure out where the statement ends and where the next one starts.
That's why the MERGE statement has to end with a ;, and why the statement that preceeds a CTE must end with a ;
T-SQL is one of the few languages that's completely white-space agnostic, yet has no statement terminators.
p.s. It's Itzik Ben-Gan
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
January 14, 2009 at 1:18 pm
This is a bit of a mess. In SQL Server 2000 semicolons were optional or in a few cases actually not allowed at the end of statements. In 2005/2008 they are mostly optional but are sometimes mandatory.
I expect semicolons will one day be mandatory for every statement in line with other DBMSs and the SQL standard. It must therefore be good practice always to use semicolons. Unfortunately there's no way to turn on stricter syntax checking for semicolons. That means no matter how careful we are they are bound to get left out somewhere in code and one day we'll all be forced to do a clean-up exercise on the code we're writing today. 🙁
January 14, 2009 at 1:23 pm
David Portas (1/14/2009)
This is a bit of a mess. In SQL Server 2000 semicolons were optional or in a few cases actually not allowed at the end of statements. In 2005/2008 they are mostly optional but are sometimes mandatory.I expect semicolons will one day be mandatory for every statement in line with other DBMSs and the SQL standard. It must therefore be good practice always to use semicolons. Unfortunately there's no way to turn on stricter syntax checking for semicolons. That means no matter how careful we are they are bound to get left out somewhere in code and one day we'll all be forced to do a clean-up exercise on the code we're writing today. 🙁
That's why I'm trying to get in the habit now of writing all my code with semicolons. Not 100% successful, but trying to get there, so I have the right habits when it becomes mandatory.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 15, 2009 at 1:57 pm
I can't wait to see the day where one just goes through ALL SP's in the database and add ; everywhere
Wait, someone should write a program for it, and make a fortune
Or maybe Microsoft can do it in the Upgrade Advisor (not just warn, but fix as well. Telling me *= is invalid is no good, fix it for me :D)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply