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.
Summary
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.
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…
5 years later. Still don't have to use them
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.
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 🙂
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.
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?
Here's another reason to start using semi-colons:
http://web.archive.org/web/*/http://web.archive.org/web/*/https://connect.microsoft.com/SQLServer/feedback/details/762588/urgent-to-fix-big-usability-problem-with-throw
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.
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?
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.
Oh, I really doubt they will make this mandatory for SQL11. I think it will take several versions to phase this in gradually.
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
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.
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. 🙂
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
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).
"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!
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.
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.