T-SQL Terminator

You may have noticed in my code examples, I always include a semicolon “;” at the end of my TSQL statements.  If you’re not doing this, then you need to start now.  Actually, if you’re not writing TSQL code as a DBA then you need to start now.

Starting in SQL Server 2005, Microsoft introduced the TSQL terminator.  This syntax is not required for most statements, but at some point in the future it will be required for all.  You can read the details on MSDN.  As with all new changes to SQL, you should be future-proofing your code.

Some statements already require the use of the terminator.  One of the best examples is the use of a Common Table Expression (CTE).  If you attempt to run this code below, you will get a syntax error.

USE AdventureWorks2008R2
WITH CTE
AS (
      SELECT
             P.FirstName
            ,P.LastName
            ,E.VacationHours
      FROM HumanResources.Employee E
            JOIN Person.Person P
                  ON E.BusinessEntityID = P.BusinessEntityID
)
SELECT * FROM CTE
WHERE VacationHours > 40;
GO

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.

To start defining a CTE, the WITH statement must be the first statement in a batch or the preceding statement must be terminated with a semicolon, even if it’s something as simple as a USE DATABASE statement.

Add the semicolon to the end of the USE statement and rerun, and you’ll get the results without any errors.

USE AdventureWorks2008R2;
WITH CTE
AS (
      SELECT
             P.FirstName
            ,P.LastName
            ,E.VacationHours
      FROM HumanResources.Employee E
            JOIN Person.Person P
                  ON E.BusinessEntityID = P.BusinessEntityID
)
SELECT * FROM CTE
WHERE VacationHours > 40;
GO

(178 row(s) affected)

This is one of the easiest code changes you can make, so starting adding it to your code today and save yourself the hassle of rewriting it tomorrow.