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
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 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)
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29735 Visits: 19006
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 Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58273 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
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8058 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
SSC-Enthusiastic
SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)

Group: General Forum Members
Points: 164 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 (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)

Group: General Forum Members
Points: 226552 Visits: 46329
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
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2120 Visits: 3708
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 Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58273 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
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3480 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