SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using semi-colon in t-sql


Using semi-colon in t-sql

Author
Message
stevoid1970
stevoid1970
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 92
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?
Matt Miller (4)
Matt Miller (4)
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12457 Visits: 18576
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. Smile

----------------------------------------------------------------------------------
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?
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23827 Visits: 9730
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
David Benoit
David Benoit
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3412 Visits: 3650
It does look cool. Very Oraclesque.... Tongue
;

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
SimonD-978595
SimonD-978595
Valued Member
Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)

Group: General Forum Members
Points: 74 Visits: 116
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88493 Visits: 45284
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


sqlvogel
sqlvogel
SSC Eights!
SSC Eights! (806 reputation)SSC Eights! (806 reputation)SSC Eights! (806 reputation)SSC Eights! (806 reputation)SSC Eights! (806 reputation)SSC Eights! (806 reputation)SSC Eights! (806 reputation)SSC Eights! (806 reputation)

Group: General Forum Members
Points: 806 Visits: 3706
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. Sad
GSquared
GSquared
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23827 Visits: 9730
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. Sad


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
Jerry Hung
Jerry Hung
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1434 Visits: 1208
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 BigGrin)

SQLServerNewbie

MCITP: Database Administrator SQL Server 2005
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search