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 12»»

What is a semicolon used for in SQL Server? Expand / Collapse
Author
Message
Posted Thursday, May 09, 2013 6:38 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 2:12 PM
Points: 27, Visits: 121
Hello All,
I have a co worker that uses the semicolon at the end of his SQL statements like

Select * From Table1(nolock) t1
Where t1.column is not null;

Besides it telling SQL Server that it is the end of the statement, is this good or normal practice? In C# it is required and is this where this has come from? I am just curious and would like to know best practices.

Thanks in advance for your time and assistance.


Tim Harms
The pain of Discipline is far better than the pain of Regret!
Post #1451388
Posted Thursday, May 09, 2013 6:41 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:16 PM
Points: 5,986, Visits: 6,931
It's not required except for certain pieces at this time, such as ending the previous statement before starting a WITH component.

However, it does no harm and is 'proper' coding, even if unnecessary. I've never made a habit of using them nor have most standards I've worked through but at the same time there's really no reason not to. It's just a style preference.

One thing they do make easier though is finding the end of long queries by simply searching for ;s.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1451389
Posted Thursday, May 09, 2013 9:05 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 2:12 PM
Points: 27, Visits: 121
Hello Craig and thank you for your reply. You make a great point and I would agree. I am going to make them a part of all of my queries.
Thanks again,

Tim Harms


Tim Harms
The pain of Discipline is far better than the pain of Regret!
Post #1451399
Posted Friday, May 10, 2013 12:39 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 12:33 PM
Points: 1,592, Visits: 1,141
In future versions of sql server the ; will be compulsory after a statement too I believe.

There's no kill switch on awesome!
Post #1451423
Posted Friday, May 10, 2013 2:55 AM


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 @ 11:47 AM
Points: 41,525, Visits: 34,442
Currently it's required at the end of a merge and the end of the statement prior to a CTE. REquirements may well increase in future as the T-SQL language gets more and more complex.

p.s. http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx



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 #1451473
Posted Friday, May 10, 2013 4:49 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 2:17 PM
Points: 3,302, Visits: 2,347
The semicolons are required in Oracle PL/SQL, but only in specific cases in T-SQL. Personally, I think it's just good coding practice. I've always done it and never run into a problem because of it. At this time, I think it boils down to mostly personal preference, but it makes sense to me to always include it.


Tally Tables - Performance Personified
Best practices on how to ask questions
Post #1451515
Posted Friday, May 10, 2013 3:48 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:03 PM
Points: 35,956, Visits: 30,244
Personally, I find the semi-colon to be a really stupid and unnecessary aggravation. T-SQL worked for years without it until CTEs came around. What's really going to tick me off is when semi-colons actually do become required, you leave one out, and the damned code is smart enough to basically say, "you're missing a semi-colon at this line".

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1451760
Posted Friday, May 10, 2013 3:56 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:47 PM
Points: 20,458, Visits: 14,082
Not a huge fan of the expected requirement of the semi-colon at the end of each statement.

They do come in handy occasionally to help distinguish the end of some big queries or complex queries.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1451763
Posted Friday, May 10, 2013 4:00 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:03 PM
Points: 35,956, Visits: 30,244
SQLRNNR (5/10/2013)
Not a huge fan of the expected requirement of the semi-colon at the end of each statement.

They do come in handy occasionally to help distinguish the end of some big queries or complex queries.


So does a comment for the next query.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1451767
Posted Friday, May 10, 2013 4:02 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:47 PM
Points: 20,458, Visits: 14,082
Jeff Moden (5/10/2013)
SQLRNNR (5/10/2013)
Not a huge fan of the expected requirement of the semi-colon at the end of each statement.

They do come in handy occasionally to help distinguish the end of some big queries or complex queries.


So does a comment for the next query.


one character or N characters

Sometimes taking the lazy 1 character approach is just so much easier.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1451769
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse