Yet another semicolon rule in SQL Server Denali

I was telling students this week about the story of the semicolon and SQL Server. And, when going over some Denali demos, it came up again.

SQL Server does not mandate semicolons as end-of-statement indicators as some databases do (as well as the ANSI standard). You can use a semicolon (or as many semicolons as you like, really) as end-of-statement, but its most often used as a statement separator for folks that submit multiple SQL statements in a database API (e.g. ADO.NET) CommandText. But you can also use CR-LF or even a single space (if the statements are simple enough) to separate statements in a batch.

In SQL Server 2005, they introduced some ambiguous (to the parser) keywords for the first time. These were CTEs ("WITH"), SEND, RECEIVE, BEGIN DIALOG CONVERSATION, and BEGIN CONVERSATION TIMER. With these, if they are not the first statement in a batch, the statement before them must end with a semicolon.

At that point folks started wondering if they should go back through all their code and put a semi after every statement. My current thought is "not for existing code (unless you're doing maintenance and need it), but yes for new code". Why? They could have more such rules in future and you'd need to memorize them all.

In SQL Server 2008, there is yet another rule, "MERGE" statement must be terminated with a semicolon.

And in Denali, it occurs again. The new "THROW" statement has the same requirement as "WITH"; if its not the first statement in the CATCH block, the statement before it must end with a semicolon.

Read more: Bob Beauchemin's Blog

Posted via email from Jasper-net