In theory, all of your T-SQL statements are supposed to end with a semicolon, like this:
|
1 2 3 |
SELECT * FROM dbo.Posts; SELECT * FROM dbo.Comments; GO |
Why? Well, SQL Server 2005’s Books Online says:
Many code examples use a semicolon (;) as a Transact-SQL statement terminator. Although the semicolon is not required, using it is considered a good practice.
You know, like flossing your teeth, and puff-puff-give. But SQL Server 2008 marked a change:
Although the semicolon is not required for most statements in this version of SQL Server, it will be required in a future version.
Whoa. When exactly is “a future version?” The current BOL page for T-SQL syntax conventions still has 2008’s vague language, so at least Microsoft has given us a heck of a long grace period to get our act together.
The gambler in me doubts that Microsoft will ever be able to enforce this rule. A ton of legacy T-SQL code would suddenly start failing, which means businesses just wouldn’t adopt a new version of SQL Server (or Azure SQL DB) that included this rule. However, the DBA in me isn’t a gambler, so when I write new code, I try to end every statement with a semicolon.
38 Comments. Leave new
I don’t think Microsoft would start enforcing semicolon just for the heck of it… I am sure there would be some other changes as well.
The statement terminators can usually be avoided with good language grammar. I bet the future languages will leave terminators out as vestige of a time when language designer were producing grammars for compilers, not for humans to read and write. e.g. JavaScript is abandoning semicolons and most modern langugaes require these only for same line statements.
When you are requiring statement terminators in T-SQL how do you actually check if these are not omitted by developers? There is no error message neither a warning from the engine. My point is that you can do your best now and still your legacy code will fail on future version of SQL Server because you never knew there was a missing terminator.
My bet is semicolons are never going to be required and even more — CTE clauses and such that require terminators at some point will be “relaxed” to allow code with no terminators e.g. rename keyword WITH -> CTE or similar grammar changes are going to happen.
“Good syntax design”, that is the key. But, for that to happen, the language architects (and their managers) must be willing to keep the language clean of such unnatural distractions. Unfortunately, the balance was broken at Microsoft cca 2008 – 2010 and most of their teams are now highly biased towards the curly braces and semicolon family of languages. Cleaner and more human-readable syntaxes are discarded and replaced (when it should be the other way around). Such was the decision to bring forth C# to replace the very successful VB.Net as the main (sometimes the only) language for SSIS script components. So, this is not a first, it’s more like a trend and it tells a lot about the C culture and bullying towards other languages (ironic, considering Microsoft’s declared goal for language parity inside .Net).
Back to the point, clean syntax design is not only possible (as proved by Bertrand Meyer and many others) but it also used to be a reality with some Microsoft teams in the past. They just need to get back to it and assign it a higher priority. There are lots of successful and relevant languages (VB, Eiffel, Python) that offer a cleaner, more human readable syntax, allowing the programmer to focus at the higher level concepts that matter rather than constantly battling punctuation errors due to misplaced semicolons or curly braces.
If we think about it, even in human languages, how frequently is the semicolon needed?
But I digress, in a programming language, the best semicolon is the one you don’t need to use!
I disagree with the stance taken in this post. Instead of taking whatever Microsoft throws at us, we need to let our voices heard (and Brent shined at that in past posts – that’s why I keep reading :-)). For those that use mostly T-SQL, why not let Microsoft know that the semicolon will make you a less efficient programmer? For those with a more complete programming background, it is clear that Microsoft can and should keep the T-SQL syntax clean of semicolons.
Semicolons are among the remnants of a C-like syntax mindset that belongs to the past century.
Millions of T-SQL coders will work harder, for no good reason. The Microsoft team can make this go away. They just need to be told to get rid of the semicolons and move this higher on their priority list.
If we don’t do that, T-SQL will gradually be “blessed” with other C-syntax artifacts and next thing you’ll know your code will suffocate in an ocean of curly braces.
…how frequently is the semicolon needed?
But I digress; in a programming language, the best semicolon is the one you don’t need to use!
Fixed it for you. 😉
“JavaScript is abandoning semicolons and most modern languages require these only for same line statements.”
I just want to check in 6.5 years later about this, haha. JS definitely hasn’t eliminated semicolons yet! (and to my knowledge, wasn’t in the process of eliminating semicolons then (minus a subset developers who don’t follow best practices.))
Nobody will ever read this, but I couldn’t let that just sit. lol
Agreed. Also some things already require semicolons, such as CTEs (before) and THROWs (after).
Happily some SQL formatting tools, like Red Gate’s SQL Prompt, already include them automatically when formatting.
Agreed with Brent, that is.
wqw as a programmer first I respectfully disagree. Failing to include semicolons in javascript will cause you no end of issues. Additionally not including a statement terminator starts to mean that line endings matter as the end of a statement which means you can now no longer wrap text as you can in things like c#.
I think you probably mean “as a curly-bracket and semi-colon language programmer.” Which makes more sense – to require a symbol for statement termination on every line except in those (rare?) cases of multi-line statements, or to only require a symbol to indicate statement continuation on the next line in those cases where it’s needed? I’d rather type an underscore 2% of the time than a semi-colon 98% of the time.
Did a Google search on “curly-brace” (https://www.google.ca/#q=curly-brace). The term seems to be as wide-spread in use as the “curly-bracket” term.
I’m with you on the later remarks 🙂
The only stab at this I can see Microsoft taking is to use the compatibility level and say if you want to write for the new level you must use semicolon. Also start putting out annoying deprecation warnings.
But it does seem a stretch that they will ever enforce this. But if they do move in that direction then you will see it coming from miles away.
I hope they never actually enforce this. I first learned SQL on SQL Server and have never used semi colons. Had no idea it was even a thing until I had to work with MySQL for something and found it highly annoying and unnecessary.
My guess might be eventual inclusion of semicolons as an element of T-SQL syntax affected by DB Compatibility Level. So Compatibility Level might suddenly become very significant for existing code (and would thus provide an incentive to clean up terminators, in order to move forward to next level.)
It would help Microsoft’s future plans if IntelliSense in the SSMS had an option to automatically add a semi-colon to finished statements. -OR- The Database engine T-SQL parser already knows where the end of the statement is. It could add them after you execute the script. -OR- Include a T-SQL lint tool that can be invoked at any time that will add semi-colons to the code.
As a died-in-the-wool classic programmer, adding a statement terminator is second nature to me and it bugs me when they aren’t there. Because I need another layer of knowledge to know what a finished statement is. Not a big deal after I have steeped myself in SQL for years and years, but I remember starting out and it was a pain.
This is a non issue. Just a self imposed good practice. I am thinking lot of us work with different DB/SQL flavors. Oracle, for instance requires a statement ending. MSSQL is maybe.
So if we are developing something, as good practice we include, always. Then we do not care where we are working, what with etc.
Per I thing MS is full of it.
The real question is: Does the gambler part of you or the DBA part of you take the lead when you have a SQL conference in Vegas?
I’m game for adding semi-colons except after BEGIN.
IF yada yada yada
BEGIN;
more stuff
END;
It just doesn’t look right to me!
Agreed. Sometimes I try to use them, but by the time I get to Begin; I think to myself, “Okay this is stupid” and end up stripping most of them out.
It’s worse when editing old code because I’m never sure if I’m going to accidentally create a hard to find bug like C’s equivalent of “for (…) ;” which skips the loop. That uncertainty kills me.
Ingenuity comes from change. When iDevices came out that limited content to what is approved on an AppStore the “jailbreak” was born to give people back control of the device they paid for. Microsoft’s execution plan was always a pain to read from their graphical view so Plan Explorer was born. Because SQL profiler lacks ability to normalize similar statements ClearTrace was born. If Microsoft does decide to make it a requirement in future versions I believe it will not be long after that a tool will be developed to mass update functions/views/SP’s/triggers/other with proper semicolon termination at the end of each statement. SQLBoost is probably 95% of the way there with its auto format logic. Sure there will be testing required to confirm functionality however this should always happen regardless when switching major versions.
My hat goes off to the individual developers and companies who contribute their hard work to the community under GNU or otherwise free of charge applications/scripts. SP Blitz is no exception.
Tony Trus
Onepax
Whoa, lots of cool stuff to check out here. I’d heard of Plan Explorer, but not the others. Thanks for posting.
Plan Explorer is just plain amazing. I use it daily and can’t say enough how valuable a tool it has become in my DBA arsenal.
Matt,
I meant to say SSMSBoost but wrote SQL Boost. It has a free community license that will need a reactivation every now and then but otherwise it’s a slick snap in for sure.
Tony Trus
Onepax
My guess is it will be included first as an option so that you can turn the new restrictive interpretation “on” or “off”. Since, we thinkers and programmers just need a consistent set of rules in order to pre-interpret how the programming language will interpret our communication to the computer, the evolution of semi-colon usage to a more restrictive and declarative paradigm is just pushing more of the interpretive work from the compiler to the programmer. I think the larger debate Brent hits on here is the fast compiler versus fast coding debate that has been around for years. Coding choices have always seemed to be the grocery store option .. buy a bag of apples versus declare a bag, pick up a bag, open the bag, and select and place the apples one by one into the bag, buy bag of apples. People who want results versus people who want control … as long as people and their preferences are different there will always be choices and smart people like Brent will always have a job learning about and then explaining those choices :).
I’ve been doing this too long – never wrote a line of sql code that didn’t have the ; as a terminator.
I’ve tried, I can’t do it 30+ years of working with DBase, Informix, Oracle, Sybase, MS Sql, MySql, posgress, etc. Plus all the programming languages
Personally, I do my best to terminate statements with a semicolon today. I actually use it to help me track the end of multi-line statements, by putting the semicolon on its own line (I put it at the end of the line for single line statements). E.g.
USE MyDB1;
SELECT *
FROM theTable
WHERE X = y
;
I say this will become law, manageable by compatibility levels – look at what happened to the oracle-style “select *= ” syntax for joins – that still exists in some of the SP’s I gingerly administer.
I seriously cannot see Microsoft ever enforcing this.
If it’s true then Microsoft should provide built in tool like Apex SQL Refactor to auto format and put semicolon where it’s needed
Semicolonoscopy: a thorough job if you’re a sql programmer, but a half-assed job if you’re a proctologist.
I use the semi colon out of habit. Mostly from using C# & MySQL and also because I read about the *future* change which would require all statements to be ended with a semi colon. I thought I’d get a head of the game.
As the years have passed I’m inclined to believe this will not happen although that would not be the first time Microsoft have proved me wrong!
Like most, it would affect legacy code and I know I probably still have old code which is missing the semi colon (probably miss it once in a while in new code too).
Nooooo!!!!!!
Semi-colons are for all those programmers who started on C-like languages and can’t be bothered to unlearn them for TSQL.
I started my SQL writing in Sybase on VT220 terminals 25 years ago and we didn’t even have semicolons on the keyboard!
Grrr!!!
*Goes off to lick gravel from’t road….
I kind of agree there. We now have a half-and-half language, part of it is BEGIN-END block-structured, part is semi-colon terminated;. Which leads to this kind of ridiculous looking code:
BEGIN;
THROW(..);
END
Not sure where the idea that BEGIN needs a semicolon came from but the SQL 99 standard does not show it.
::=
[ ] BEGIN [ [ NOT ] ATOMIC ]
[ ] [ ] [ ]
[ ] END [ ]
I am going to start worry about semicolons once the SSMS scripting tools start generating scripts with semicolons.
Wow it really messed that up, trying again
\ ::=
[ \ \ ] BEGIN [ [ NOT ] ATOMIC ]
[ \ ] [ \ ] [ \ ]
[ \ ] END [ \ ]
compound statement ::=
[ beginning label colon ] BEGIN [ [ NOT ] ATOMIC ]
[ local declaration list ] [ local cursor declaration list ] [ local handler declaration list ]
[ SQL statement list ] END [ ending label ]
I was wondering how the semicolon snuck itself into the T-SQL language. Sounds like it was the C people – always messing up the good life of programming. Surprised they didn’t replace
BEGIN/ENDwith{/}while they were at it. Pretty stupid, literally, that they introduced the semicolon. I refuse to do it. Part of what attracts me to the SQL language is the lack of needless syntax.Need a
THROWstatement?;THROW …
Need
MERGE?MERGE …
…
…
;
Only put the semicolon in when the compiler complains, otherwise it is staying out. If the programmers of SQL are so lazy that they require us to start putting in semicolons I say, work harder guys. Stop being lazy. Seriously.
On a little liter side of things:
https://img.ifcdn.com/images/0e3caac04665acbe7de11700094d8509d2141bea1a5b849e9a679aa2f015c60a_1.jpg
I love the semicolons so much I want to do:
begin
;throw;
end
See? No semicolon after BEGIN, nice ahhh. I would also like to have an option to REQUIRE semicolons (except on BEGIN/END cause it does look dumb, just does, we can all agree its just dumb).
set strict_semicolons on; –obviously need that trailing semicolon 🙂
This way my SQL programmers can act like real programmers and stop complaining that the extra character makes their right pinky hurt. There’s just nothing good about saying “oh let me write some declarative stuff and some procedural stuff in a natural language-ish syntax and hope that the parser knows what I mean and that future keywords and syntax changes don’t make my current implicit statement breaks ambiguous… uuuuuu my pinky is hurting again…”
lol, That’s awesome.
Real programmers use white-spaced programming languages. You-know, because we like to have the computer do the dumb stuff for us.