When should I not use a semicolon?

Question

Or: What is not a T-SQL statement?

Except to resolve ambiguity, T-SQL syntax does not require a semicolon to terminate a statement. Despite this, Itzik Ben-Gan recommends using a semicolon to terminate a T-SQL statement because it makes code cleaner, more readable, easier to maintain, and more portable.

I don't know a precise definition of what a valid T-SQL statement is, so I might be confused here. But as far as I know, a BEGIN...END block is a T-SQL statement, so should be terminated by a semicolon. For example:

IF OBJECT_ID('tempdb.dbo.#TempTable') IS NOT NULL BEGIN   DROP TABLE #TempTable; END; 

The code example in Microsoft's BEGIN...END documentation supports this conjecture:

USE AdventureWorks2008R2; GO BEGIN TRANSACTION; GO IF @@TRANCOUNT = 0 BEGIN     SELECT FirstName, MiddleName      FROM Person.Person WHERE LastName = 'Adams';     ROLLBACK TRANSACTION;     PRINT N'Rolling back the transaction two times would cause an error.'; END; ROLLBACK TRANSACTION; PRINT N'Rolled back the transaction.'; GO /* Rolled back the tranaction. */ 

Itzik Ben-Gan contradicts this in the code example of Excercise 1-1 of T-SQL Fundamentals:

SET NOCOUNT ON; USE TSQLFundamentals2008; IF OBJECT_ID('dbo.Nums', 'U') IS NOT NULL DROP TABLE dbo.Nums; CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);  DECLARE @i AS INT = 1; BEGIN TRAN   WHILE @i <= 100000   BEGIN     INSERT INTO dbo.Nums VALUES(@i);     SET @i = @i + 1;   END COMMIT TRAN SET NOCOUNT OFF; 

Microsoft's Transact-SQL Syntax Conventions document states that the semicolon "will be required in a future version" of T-SQL.

Commenting on Microsoft's intention to require the semicolon in a future version of T-SQL, Itzik notes some exceptions that aren't supposed to be terminated:

So far it was a requirement to use a semicolon only in specific cases. Now it looks like the plan is to make it a required terminator for all* T-SQL statements in some future version of SQL Server.

(*) Naturally there are cases that aren’t supposed to be terminated with a semicolon; those include (but are not limited to):

• BEGIN

• BEGIN TRAN

• IF

• ELSE

• WHILE

• BEGIN TRY

• END TRY

• BEGIN CATCH

Itzik seems to be consistent with himself, but Microsoft itself does not follow his recommendations. Compare Microsoft's BEGIN TRANSACTION; and Itzik's BEGIN TRAN in the previous examples.

In the code I maintain, I have seen even the BEGIN keyword terminated by semicolon:

IF @HasWidget = 0x1 BEGIN;   SELECT WidgetID   FROM tbWidgets; END; 

I believe a T-SQL parser may consider the semicolon following the BEGIN keyword to terminate an empty statement rather than terminate the BEGIN keyword itself; I don't believe that BEGIN itself is a valid T-SQL statement.

This conjecture is supported by the fact that SQL Server 2008 successfully parses and executes the following query:

SELECT 0;; 

It's so confusing because there is no widely available specification of the T-SQL language, like the Java Language Specification for Java, so nowhere is there a formal definition of a T-SQL statement.

Am I wrong? Does such a specification exist for T-SQL, and is it publicly available?

Otherwise, should just I believe what Itzik says?

Answer 1

T-SQL syntax does not require a semicolon to terminate a statement.

Actually, this is deprecated¹. I can't remember for sure, but I think you can still get away with not using them in the upcoming SQL Server 2012, but some version after that will likely require a semi-colon for every statement. Using a semi-colon is also technically required by the ansi standard. The point is that now is the time to get in the habit of using one for every statement.

As a practical matter, I don't expect them to follow through with this directly. Rather, I expect SQL Server Management Studio and other development tools to first start issuing warnings instead of errors, perhaps for several versions. This will help developers find and fix all the old non-compliant code. But that doesn't lessen the message: semi-colons are coming, and soon.

For a simple heuristic on when not to use a semi-colon, think of the code as if it were a procedural language that used curly brackets for blocks, like C/C++. Statements that would be paired with an opening (not closing) curly bracket if written in the procedure language should not get a semi-colon.

¹_{It's almost all the way at the bottom of the page}

Answer 2

Summary, based on the OP's original, quoted list.

Yes semi-colon:

• BEGIN TRAN;

No semi-colon:

• BEGIN
• IF
• ELSE
• WHILE
• BEGIN TRY
• END TRY
• BEGIN CATCH

Also, use them after END and END CATCH.

Details:

BEGIN TRAN is a statement and should be terminated with a semi-colon.

Microsoft's documentation notes the optional semi-colon:

BEGIN { TRAN | TRANSACTION }      [ { transaction_name | @tran_name_variable }       [ WITH MARK [ 'description' ] ]     ] [ ; ] 

Microsoft's example has semi-colons:

BEGIN TRAN T1; UPDATE table1 ...; BEGIN TRAN M2 WITH MARK; UPDATE table2 ...; SELECT * from table1; COMMIT TRAN M2; UPDATE table3 ...; COMMIT TRAN T1; 

Both of the above are from:

https://msdn.microsoft.com/en-us/library/ms188929(v=sql.90).aspx

They match the current documentation:

https://msdn.microsoft.com/en-us/library/ms188929(v=sql.120).aspx

As for BEGIN...END, the Microsoft documentation does not provide clear guidance.

The definition has no semi-colon:

BEGIN      {      sql_statement | statement_block       }  END 

However, their example shows a semi-colon after END:

IF @@TRANCOUNT = 0 BEGIN     SELECT FirstName, MiddleName      FROM Person.Person WHERE LastName = 'Adams';     ROLLBACK TRANSACTION;     PRINT N'Rolling back the transaction two times would cause an error.'; END; 

https://msdn.microsoft.com/en-us/library/ms190487.aspx

That trailing semi-colon is not consistent with Microsoft's own documentation for IF control of flow language construct:

IF Boolean_expression       { sql_statement | statement_block }  [ ELSE       { sql_statement | statement_block } ]  

Neither that definition nor their code example shows any semi-colon:

DECLARE @compareprice money, @cost money  EXECUTE Production.uspGetList '%Bikes%', 700,      @compareprice OUT,      @cost OUTPUT IF @cost <= @compareprice  BEGIN     PRINT 'These products can be purchased for less than      $'+RTRIM(CAST(@compareprice AS varchar(20)))+'.' END ELSE     PRINT 'The prices for all products in this category exceed      $'+ RTRIM(CAST(@compareprice AS varchar(20)))+'.' 

https://msdn.microsoft.com/en-us/library/ms182717(v=sql.110).aspx

However, their ELSE documentation, while also not showing any semi-colon in the definition, does show one in the example, after the final END.

Definition:

IF Boolean_expression { sql_statement | statement_block }      [ ELSE { sql_statement | statement_block } ]  

Example:

IF 1 = 1 PRINT 'Boolean_expression is true.' ELSE PRINT 'Boolean_expression is false.' ; 

https://msdn.microsoft.com/en-us/library/ms182587(v=sql.110).aspx

The ANSI standard doesn't resolve the ambiguity because these are non-standard extensions:

Control-of-flow statements are not covered by the ANSI SQL standard because these are proprietary SQL extensions. The SQL Server Books Online is sketchy on the subject and many of the examples (as of this writing) are inconsistent and do not always include statement terminators. Furthermore, control-of-flow statement blocks are confusing due to the many variations, nesting, and optional BEGIN/END specifications.

http://www.dbdelta.com/always-use-semicolon-statement-terminators/

However, the behavior of the server sheds some light. The following is not a syntax error in SQL Server 2005:

DECLARE @foo int; IF @foo IS NULL BEGIN     WITH Blah AS     (         SELECT             'a' AS a     )     SELECT         a     FROM        Blah; END 

So the BEGIN itself does not require a semi-colon. However, the following does produce a syntax error in SQL Server 2005:

DECLARE @foo int; IF @foo IS NULL BEGIN     WITH Blah AS     (         SELECT             'a' AS a     )     SELECT         a     FROM        Blah; END WITH Blah2 AS (     SELECT         'a' AS a ) SELECT     a FROM        Blah2; 

The above results in this error:

Msg 319, Level 15, State 1, Line 13 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.

It also throws that error in SQL Server 2008 R2.

It gets even more confusing. Microsoft's documentation for TRY...CATCH shows an optional semi-colon after the END CATCH, and their examples are consistent with that.

BEGIN TRY      { sql_statement | statement_block } END TRY BEGIN CATCH      [ { sql_statement | statement_block } ] END CATCH [ ; ] 

However, if you have a CTE immediately after a BEGIN TRY, without a semi-colon, it will throw an error.

BEGIN TRY     WITH Blah AS     (         SELECT             'a' AS a     )     SELECT         a     FROM        Blah; END TRY BEGIN CATCH END CATCH 

In SQL Server 2008 R2, the above batch throws this error:

Msg 319, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

The error implies that BEGIN TRY is a statement (which it isn't), and that a semi-colon "fixes" the issue (which it does). That's right, this works:

BEGIN TRY;     WITH Blah AS     (         SELECT             'a' AS a     )     SELECT         a     FROM        Blah; END TRY BEGIN CATCH END CATCH 

However, Microsoft says that's not good practice:

Posted by Microsoft on 12/29/2009 at 12:11 PM I am resolving the corresonding SQL11 bug as "by design". Here is the explanation:

The semicolon between END TRY and BEGIN CATCH should not be allowed, because they are actually not different statements, but parts of the same TRY-CATCH statement. We only allow semicolons when they separate two statements in a sequence.

A word of explanation why then we allow semicolons after BEGIN TRY and BEGIN CATCH. These keywords serve as opening "parentheses" that start an embedded statement sequence. Semicolons after BEGIN TRY/BEGIN CATCH get parsed as part of that embedded sequence, with the first statement in the sequence being empty. While we allow this syntax, I would not recommend it as a good coding practice because it creates a wrong impression of BEGIN TRY/BEGIN CATCH being independent, standalone statements.

The recommended way to handle that situation is with an extra BEGIN...END for clarity:

BEGIN TRY     BEGIN         WITH Blah AS         (             SELECT                 'a' AS a         )         SELECT             a         FROM        Blah;     END END TRY BEGIN CATCH END CATCH 

However, that END before the END TRY should probably have a semi-colon. After all, this will throw an error:

BEGIN TRY     BEGIN         WITH Blah AS         (             SELECT                 'a' AS a         )         SELECT             a         FROM        Blah;     END     WITH Blah2 AS     (         SELECT             'b' AS b     )     SELECT         b     FROM        Blah2; END TRY BEGIN CATCH END CATCH 

Maybe always preceding a CTE WITH a semi-colon isn't so silly.