See the full index.
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):
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.
To prevent this from happening to you, all relevant statements should get a semi-colon. I wouldn't go to the extent of inspecting all of your existing code and adding them, but if I were re-visiting a module for some other enhancement, I would take the few seconds and add any semi-colons that were missing. 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 recently!
Someday, I may use MERGE.
Well, not any time soon. But a similar requirement exists for
MERGE – it must be properly terminated with a semi-colon. Otherwise, you get this error message:
A MERGE statement must be terminated by a semi-colon (;)
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. 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:
Although the semicolon isn't required for most statements in this version of SQL Server, it will be required in a future version.
- Deprecated Database Engine Features in SQL Server 2016
(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.
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.
See the full index.