Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using semi-colon in t-sql Expand / Collapse
Author
Message
Posted Wednesday, January 14, 2009 12:21 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 12:51 PM
Points: 12, Visits: 88
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?
Post #636549
Posted Wednesday, January 14, 2009 12:39 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:28 AM
Points: 7,179, Visits: 15,775
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?
Post #636568
Posted Wednesday, January 14, 2009 12:42 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:58 AM
Points: 13,872, Visits: 9,600
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
Post #636571
Posted Wednesday, January 14, 2009 12:44 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 8:02 AM
Points: 2,049, Visits: 3,596
It does look cool. Very Oraclesque.... :P
;


David

@SQLTentmaker
SQL Tentmaker
“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Post #636575
Posted Wednesday, January 14, 2009 12:50 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, February 3, 2010 4:26 AM
Points: 68, 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
Post #636585
Posted Wednesday, January 14, 2009 1:13 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 8:36 AM
Points: 40,615, Visits: 37,080
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 2008, MVP
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

Post #636620
Posted Wednesday, January 14, 2009 1:18 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 2:32 AM
Points: 451, Visits: 3,470
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.


David
Post #636624
Posted Wednesday, January 14, 2009 1:23 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:58 AM
Points: 13,872, Visits: 9,600
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
Post #636635
Posted Thursday, January 15, 2009 1:57 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 8:26 AM
Points: 775, Visits: 1,203
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)


SQLServerNewbie

MCITP: Database Administrator SQL Server 2005
Post #637568
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse