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:
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.
[Hmm, now I'm thinking the title of this post sounds a little dirty.]