Rules of SQL formatting – Terminating SQL statements with semicolons

This article explains usage of a semicolon to terminate SQL statements and differences between the GO command and a semicolon.

Terminating SQL statements with semicolons was optional in SQL Server 2000 and in some cases actually not allowed. In SQL Server 2005, the introduction of Common Table Expressions (CTEs) and Service Broker Statements brought new formatting rules. Terminating a statement that preceded the Service Broker Statements (SEND, RECEIVE, BEGIN DIALOG CONVERSATION, and BEGIN CONVERSATION TIMER) and the CTEs (the WITH statement) became mandatory if those statements were not first in the batch. For example, running the following query where the WITH statement is not the first in the batch will result in a T-SQL syntax error:

INSERT INTO Emails VALUES (1, 'M', 'S')
INSERT INTO Emails VALUES (3, 'R', NULL)
WITH Ms
    AS ( SELECT FirstName ,
                LastName
           FROM Emails
          WHERE LastName = NULL )
    SELECT *
      FROM Ms;

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

Terminating the statement before the WITH keyword with a semicolon will all it to execute without errors:

;WITH Ms AS (...) ...

SQL Server 2008 introduced a new rule for terminating the MERGE statement with a semicolon. Failing to terminate the following MERGE statement also results with a syntax error:

MERGE INTO Person.Person AS P
USING Person.EmailAddress AS EA
ON P.BusinessEntityID = EA.BusinessEntityID
WHEN MATCHED THEN
UPDATE SET ModifiedDate = EA.ModifiedDate

Msg 10713, Level 15, State 1, Line 5
A MERGE statement must be terminated by a semi-colon (;).

In SQL Server 2012 the THROW statement was introduced with the same rule for terminating the SQL statement as the WITH statement. If the THROW statement is not the first statement in the CATCH block the statement that precedes it must be terminated with the semicolon. Executing the following query:

BEGIN TRY
INSERT INTO Person.ContactType( Name, ModifiedDate)
VALUES( 'nj', null);
END TRY
BEGIN CATCH
IF ERROR_NUMBER() = 515
BEGIN
PRINT 'NULL violation';
END
BEGIN
PRINT 'Re-throw'
THROW; 
END
END CATCH;

results with a syntax error because the PRINT ‘Re-throw’ command is not terminated with a semicolon:

Msg 102, Level 15, State 1, Line 12
Incorrect syntax near ‘THROW’.

Not terminating statements with a semicolon is also a deprecated feature in SQL Server 2012.

Note that use of the semicolon for terminating statements differs from the GO command.

Client application programming interfaces (APIs) such as ADO.NET provide you with methods for submitting a batch of code to SQL Server for execution. SQL Server utilities such as SQL Server Management Studio, SQLCMD, and OSQL provide a client command called GO that signals the end of a batch. Note that the GO command is a client command and not a T-SQL server command [1]

Ending SQL statements with the GO command will sometimes seem valid like in the example below where the GO command signals the end of a batch and the query executes without errors because the WITH statement is now first in the batch. But this does not terminate the statement before the WITH statement properly:

INSERT INTO Emails VALUES (1, 'M', 'S')
INSERT INTO Emails VALUES (3, 'R', NULL)
GO
WITH Ms
    AS ( SELECT FirstName ,
                LastName
           FROM Emails
          WHERE LastName = NULL )
    SELECT *
      FROM Ms;

Using ApexSQL Refactor, all SQL statements can be terminated with a semicolon to prevent syntax mistakes in future versions of SQL Server and format SQL code and scripts to comply with the SQL formatting rules.

Under the ApexSQL Refactor menu, choose the Options command and the Options window will be shown. In the Formatting tab, select the Always use statement terminator option:

INSERT INTO Emails VALUES (1, 'M', 'S')
INSERT INTO Emails VALUES (3, 'R', NULL)
WITH Ms
    AS ( SELECT FirstName ,
                LastName
           FROM Emails
          WHERE LastName = NULL )
    SELECT *
      FROM Ms

This option will terminate all statements in code above with a semicolon:

INSERT INTO Emails VALUES (1, 'M', 'S');
INSERT INTO Emails VALUES (3, 'R', NULL);
WITH Ms
    AS ( SELECT FirstName ,
                LastName
           FROM Emails
          WHERE LastName = NULL )
    SELECT *
      FROM Ms;

References:
[1] Microsoft® SQL Server ® 2012 T-SQL Fundamentals – Itzik Ben-Gan

Useful resources:
Books Online for SQL Server – THROW (Transact-SQL)
Books Online for SQL Server – WITH common_table_expression (Transact-SQL)
Books Online for SQL Server – GO (Transact SQL)
Books Online for SQL Server – Deprecated Database Engine Features in SQL Server 2012