What is a semicolon used for in SQL Server?

Question

What is a semicolon used for in SQL Server?

SQLArnold

SSCommitted

Points: 1847
More actions
May 9, 2013 at 6:38 pm

#394748

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.
The pain of Discipline is far better than the pain of Regret!

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

SQLArnold SSCommitted Points: 1847 More actions · Answer 2

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

The pain of Discipline is far better than the pain of Regret!

Loundy SSCertifiable Points: 6080 More actions · Answer 3

In future versions of sql server the ; will be compulsory after a statement too I believe.

[font="Times New Roman"]There's no kill switch on awesome![/font]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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, 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

Ed Wagner SSC Guru Points: 287026 More actions · Answer 5

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
String Splitting with True Performance
Best practices on how to ask questions

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

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, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

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, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 10

SQLRNNR (5/10/2013)
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.

The N characters you're talking about are going to be there anyway because I document my code. 🙂

--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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

SQLRNNR (5/10/2013)
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.

And the semicolon ENDs the statement where as a comment may not as you could have comments embedded to describe derived tables and their use.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 12

I understand what the semi-colon can do so far as ending a statement but it worked just fine before without it before. I don't know what your formatting convention is but I don't need a semi-colon at the end of even the longest of queries to tell me that a new query has started whether the code has any comments or not.

--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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 13

Jeff Moden (5/10/2013)
SQLRNNR (5/10/2013)
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.
The N characters you're talking about are going to be there anyway because I document my code. 🙂

I know. And I typically document mine (though not as much of a book as yours 😉 ) :-D:-D

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

Jeff Moden (5/10/2013)
I understand what the semi-colon can do so far as ending a statement but it worked just fine before without it before. I don't know what your formatting convention is but I don't need a semi-colon at the end of even the longest of queries to tell me that a new query has started whether the code has any comments or not.

I come from a COBOL environment where (at one time) every sentence had to be terminated by a period. When I started learning SQL (T-SQL) I saw the optional semicolon and just started using it, and I still do today.