THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Aaron Bertrand

Aaron is a senior consultant for SQL Sentry, Inc., makers of performance monitoring and event management software for SQL Server, Analysis Services, and Windows. He has been blogging here at sqlblog.com since 2006, focusing on manageability, performance, and new features; has been a Microsoft MVP since 1997; tweets as @AaronBertrand; and speaks frequently at user group meetings and SQL Saturday events.

Ladies and gentlemen, start your semi-colons!

Almost a year ago now, I wrote up a blog post detailing the things that I strive to do when writing T-SQL modules (mostly stored procedures).  One of the things that I have been doing since my very first common table expression (CTE) back in the Yukon beta is ending all relevant statements with statement terminators (semi-colons).  A few responses to the above post indicated that they don't currently use statement terminators, and had no interest in starting.  Let's quickly recap the reasons why I started making this a habit.
 

Today, I use CTEs and Service Broker.

Unless it is the first statement in a batch, the previous statement prior to a CTE declaration needs to be terminated with a semi-colon.  This is also true for Service Broker commands (e.g. SEND ON CONVERSATION).  Some people "fix" this by simply writing code ike this:

;WITH foo AS (...) ...

And typically when I post CTE examples to newsgroups / forums, I do this, to avoid the inevitable comeback with this error message attached (because you never know where they are going to jam your example, and then complain that it didn't work):

.Net SqlClient Data Provider: Msg 319, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'with'. If this statement is a common table expression
or an xmlnamespaces clause, the previous statement must be terminated with a semicolon

But I think that syntax is far too ugly and counter-intuitive for my production code (YMMV).  So for this reason, all relevant statements in my code get a semi-colon.  I didn't go to the extent of inspecting all of my existing code and adding them, but if I was re-visiting a module for some other enhancement, I would take the few seconds and add any semi-colons that were missing.  I didn't feel like I needed to do this, but thought it best to future-proof my code as much as possible, when convenient.  And of course when writing new code, I would always terminate.  This is probably best demonstrated by scrolling through my blog posts over the past year or two, and trying to spot any examples where I neglected to add a semi-colon.  I doubt you'll find many!
 

Someday, I may use MERGE.

In SQL Server 2008, a similar requirement was added for MERGE: you can't run a MERGE statement unless it is properly terminated with a semi-colon.  Otherwise, you get this error message:

.Net SqlClient Data Provider: Msg 10713, Level 15, State 1, Line 21
A MERGE statement must be terminated by a semi-colon (;)

So because of this, the set of reasons for future-proofing your code in this way has doubled: while your CTEs and MERGE statements obviously work today, because your procedure compiled, a user modifying them later might break them, and this will cause an extra debugging step.  So at this point, I was debating whether it might make sense to comb through all of my code and add semi-colons where appropriate, but was still sitting quite squarely on the fence.
 

Not convinced?  Hold on, there's more!

In the previous post, I suggested that someday Microsoft will make statement terminators mandatory.  Well, they have.  Statements that DON'T end with a proper statement terminator are on the deprecation list, which means that at some point, any statement that doesn't end with a semi-colon will cause an error similar to those listed above.  Sounds funny, but it is true.  If you don't believe me, please review these topics in Books Online:

Transact-SQL Syntax Conventions (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms177563.aspx
(See the second-last row in the first table.)

Deprecated Database Engine Features in SQL Server 2008
http://msdn.microsoft.com/en-us/library/ms143729.aspx
(It's a lot of scrolling; search the page for "semicolon.")

I'm not sure if they're doing this because the ANSI standard now declares statement terminators as mandatory, or because as the T-SQL language gets more and more complex, the complexities in the parser code multiply, or a combination of both.  But I am no longer on the fence: it is on my "long-term task list" to systematically attack chunks of T-SQL modules and fix these pesky situations where statement terminators are missing.
 

Conclusion

I've outlined several good reasons to get into the habit of ending all of your T-SQL statements with semi-colons, at least for work going forward.  And I may have even painted a picture that shows you it may worthwhile to go back and "fix" your existing codebase -- maybe coupled with a simultaneous code review of some other kind, to help justify the effort.  Because "a future version of SQL Server" might be closer than you think.
  

Published Thursday, September 03, 2009 2:13 PM by AaronBertrand

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Jonathan Kehayias said:

Very nice, especially since the competing RDMBS systems (at least Oracle) has required this for a while now.  I am pretty good about using terminators in my code because of time working with Oracle over the last two years.

September 3, 2009 2:44 PM
 

Whitney Weaver said:

Aaron,

I've been wondering when this would become official.  I have had a couple MS people mention this in the past but always in terms of "some day".  

Personally, I always use them and try to get others to do so if working on the same project with me.  Now I have another place to point them, thanks for that.

Hadn't considered the dirty title angle.  But thanks for kicking off the parade of jokes going through my head now.

September 3, 2009 2:49 PM
 

Brian Tkatch said:

"But I think that syntax is far too ugly and counter-intuitive for my production code (YMMV). "

Exactly!

I used to Oracle, so the lack of semi-colons looks funny to me. Though, i still wan to be able to highligh half a query and run it, semi-colon or not!

September 3, 2009 3:25 PM
 

AaronBertrand said:

Brian, that's an interesting point, I hadn't thought about the partial ad-hoc query.  I wonder if the enforcement could only kick in when the batch has more than one statement (but then part of the point of the semi-colon is so that the parser can identify individual statements, so this is kind of chicken and egg).  Or if you could intentionally run SSMS in an enforcement-bypass mode (kind of like a debug mode).

September 3, 2009 3:32 PM
 

jamiet said:

There is an ever-so-slightly-related other reason to use semicolons.

In Oracle tools it is general convention to be able to position your cursor within a statement, hit F9, and only that statement will execute.

There is a growing body of people who want similar in SSMS (there's a Connect submission for it somewhere but I couldn't find it cos Connect is playing up. Again.) and one can assume that this may rely on the use of semicolons for termination. So, in anticipation of them MAYBE introducing this very useful feature in the next version - use semicolons!!!

-Jamie

September 3, 2009 5:51 PM
 

Wiseman82 said:

Thanks for the heads up!  I've been using semi-colons a lot more since the introduction of SQL 2005 (mainly because they are required for CTEs), but I can't say that I use them religiously. If it's deprecated, we might as well all get into the habit of using them now and also to make our lives easier when we upgrade to the next version of SQL Server (or whatever version they finally remove it from).

Thanks. :-)

September 4, 2009 3:24 AM
 

GlennAlanBerry said:

I agree that it is a good idea to terminate statements with semicolons, since it will finally be required in a future version (I'm guessing SQL 12) of SQL Server.

I make my students do it in my T-SQL classes, so they are used to it at least.

September 4, 2009 11:33 AM
 

gcardoso said:

Very nice post!

I don`t have the habit of use semicolon terminator in my T-SQL code, but now i will adopt.  Who know, the Microsoft implement semicolon terminator as a mandatory syntax in the version of SQL 2011, isn`t?

gcardoso

September 4, 2009 1:19 PM
 

AaronBertrand said:

Oh, I really doubt they will make this mandatory for SQL11.  I think it will take several versions to phase this in gradually.

September 4, 2009 1:23 PM
 

JohnG said:

I've been coding SQL Server T-SQL this way for years.  Having started with IBM's DB2 back in the early 80s and having coded with Oracle from V5 through 10g, which all require semicolons, it was a no-brainer.  It also made the porting of code from SQL Server to Oracle a lot easier.

SQL Server has been the "odd" animal for too long.

September 9, 2009 2:47 PM
 

Aaron Bertrand said:

A few weeks ago, I wrote a post about forming a new habit: always terminate statements with semi-colons

October 6, 2009 11:21 AM
 

Aaron Bertrand said:

I've been fairly distracted this past week with my " Bad habits to kick " series, so apologies for being

October 17, 2009 2:13 PM
 

The Rambling DBA: Jonathan Kehayias said:

Setting up Database Mail in SQL Server 2005 and 2008 is a common task that is performed post server setup.

August 23, 2010 11:31 AM
 

Aaron Bertrand said:

Sadly, THROW is not mentioned on the Programmability Enhancements (Database Engine) topic of Denali's

November 22, 2010 12:20 PM
 

Aaron Bertrand said:

Earlier, I documented the system objects that have changed in Denali . One that caught my eye was a slew

November 30, 2010 2:37 PM
 

Aaron Bertrand said:

Today I was in Chicago for SQL Saturday #67 to present my "What's New in SQL Server Denali" deck. You

March 26, 2011 2:24 PM
 

Rand E. Gerald said:

As I mentioned in your SQL Saturday #67 session:

If the use of semicolons to terminate statements may soon become mandatory, how come they are not automatically included in snippets and templates?

March 29, 2011 1:00 PM
 

AaronBertrand said:

Rand, it's a really good question. I'm afraid for now the answer is simply that Microsoft is saying, "do as I say, not as I do." I've complained about a lot of the code sample practices they've used over the years, but it takes a lot of momentum to make any changes.

March 29, 2011 1:07 PM
 

Aaron Bertrand said:

T-SQL Tuesday, the invention of Adam Machanic (@AdamMachanic), is what he calls a recurring, revolving

July 12, 2011 8:45 AM
 

AaronBertrand said:

September 25, 2012 6:49 PM
 

Radu Popa said:

What hapenned to the old mantra of trying to "make life easier for the customer", developers like us, as opposed to Microsoft parser writers?

Microsoft and the SQL comunity is giving-up too easily to the Terminatists, as Bertrand Meyer would put it.

This is a 30 years old debate in language theory and Bertrand Meyer describes it very concisely in his highly regarded book [Object-Oriented Software Construction]. I have the 2nd edition and [The War of the Semicolons] section (pages 897 - 899) describes the four style trends:

• Terminatist: every instruction, declaration or assertion clause ends with a semicolon.

• Separatist: semicolons appear between successive elements but not, for example, after the last declaration of a feature or local clause.

• Moderately Separatist: like the Separatist style, but not worrying about extra semicolons that may appear as a result of habit or of elements being moved from one context to another.

• Discardist: no semicolons at all (except as per the Semicolon Style principle below).

Bertrand Meyer's presentation is entertaining and to the point. Unfortunately, I will have to limit my summary here. Suffice is to say that:

1. He chose (and that was over 25 years ago) to leave the semi-colon optional, as a programmers choice of style. His [Semicolon Syntax rule] states: "Semicolons, as markers to delimit instructions, declarations or assertion clauses, are optional in almost all the positions where they may appear"

2. And to clarify his position, he concludes the section with:

"You should defer to your own taste as long as it is consistent and respects the Semicolon Style principle. (As to this book: for a while I stuck to the original Separatist style, more out of habit than of real commitment; then, hearing the approach of the third millenium and its call to start a new life free of antique superstitions, I removed all the

semicolons over a single night of utter debauchery.)"

And I'm siding with Bertrand Meyer on this one :-)

If Bertrand Meyer could do it, why can't we have the same in SQL?

December 21, 2012 5:16 PM
 

AaronBertrand said:

Radu, to be fair, Bertrand Meyer did not implement a modern RDBMS where customers scream for new features and also want to carry along all of the old baggage so that their old code does not break. At some point, something has to give - you either have to invent completely meaningless and semantically incorrect keywords or you have to stop adding such features.

December 21, 2012 5:22 PM
 

Radu Popa said:

That might be true Aaron, and I agree with "something has to give" when it comes to it.

That being said, I much prefer to "Give it five minutes" of thought (http://37signals.com/svn/posts/3124-give-it-five-minutes), when possible. In the case of the semi-colon, the SQL community had decades at their disposal to think about it. The current trend indicates shallowness (not enough people gave it a thorough thought).

Firefighting has its place in the world, but it should be the exception, not the norm. I just don't think it should be our way of leaving.

And, to be fair to Bertrand Meyer, I guess he would have gladly helped but I'm afraid he was dismissed by those in charge at the time ...

Sorry if I sound too critical (of the semi-colon); please rest assured this is nothing personal :-)

December 22, 2012 10:01 AM
 

Aaron Bertrand said:

Oh it doesn't bother me. If you want to be defiant about using semi-colons. But if you continue using SQL Server, you're not going to have much choice. You can start embracing it now, or just defer that work until later. For me, it's easier to pay that marginal cost now than wait until a major upgrade to suddenly have to fix all that code.

December 22, 2012 10:11 AM
 

Matt said:

5 years later. Still don't have to use them

August 27, 2014 4:32 PM
 

AaronBertrand said:

Matt while true, you've also added 5 more years of non-future-proof code to your codebase. If this deprecation ever really happens, you're going to be hurting...

August 27, 2014 4:44 PM

Leave a Comment

(required) 
(required) 
Submit

About AaronBertrand

...about me...

This Blog

Syndication

Archives

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement