In SQL Server, the semicolon (;) is used as a statement terminator. While it is not mandatory for most T-SQL statements, future versions of SQL Server may require its usage. This article explores the importance of using semicolons in certain T-SQL statements and provides examples of when they are necessary.

Common Table Expression (CTE) using Semicolon Terminator

One T-SQL statement that requires a semicolon is the creation of a common table expression (CTE). Without the semicolon, errors can occur. For example:

SELECT 1 AS '1'
 
WITH Numbers AS 
(
   SELECT 1 AS n
   UNION ALL
   SELECT n+1
   FROM Numbers
   WHERE n<100
)
SELECT n AS Number
FROM Numbers
OPTION (MAXRECURSION 0)

In this case, an error is thrown because the previous statement of the CTE is not terminated with a semicolon. To fix this, simply add a semicolon after the first SELECT statement:

SELECT 1 AS '1'
 
; WITH Numbers AS 
(
   SELECT 1 AS n
   UNION ALL
   SELECT n+1
   FROM Numbers
   WHERE n<100
)
SELECT n AS Number
FROM Numbers
OPTION (MAXRECURSION 0)

MERGE Statement using Semicolon Terminator

The MERGE statement, which allows for INSERT, UPDATE, and DELETE operations in a single command, also requires a semicolon. Without it, errors can occur. For example:

MERGE ##tmpTableA AS t 
USING ##tmpTableB AS s
ON s.ID=t.ID 
WHEN MATCHED AND t.Val<>s.Val
    THEN UPDATE 
        SET t.Val=s.Val 
WHEN NOT MATCHED
    THEN INSERT (ID, Val)
        VALUES(s.ID, s.Val)
 
SELECT * FROM ##tmpTableA

To resolve this issue, simply add a semicolon after the MERGE statement:

MERGE ##tmpTableA AS t 
USING ##tmpTableB AS s
ON s.ID=t.ID 
WHEN MATCHED AND t.Val<>s.Val
    THEN UPDATE 
        SET t.Val=s.Val 
WHEN NOT MATCHED
    THEN INSERT (ID, Val)
        VALUES(s.ID, s.Val)
;
 
SELECT * FROM ##tmpTableA

THROW Statement using Semicolon Terminator

The THROW statement, used to raise exceptions and transfer execution to a CATCH block, also requires a semicolon. Without it, errors can occur. For example:

BEGIN TRY  
 
   INSERT INTO ##tmpTableA(ID, Val)
   VALUES (1, 1000)
 
END TRY  
BEGIN CATCH  
 
    PRINT 'Error'
    THROW
   
END CATCH

To fix this issue, add a semicolon after the first command in the CATCH block:

BEGIN TRY  
 
   INSERT INTO ##tmpTableA(ID, Val)
   VALUES (1, 1000)
 
END TRY  
BEGIN CATCH  
 
    PRINT 'Error';
    THROW
   
END CATCH

By following these guidelines and using semicolons where necessary, you can ensure that your T-SQL code runs smoothly and avoids any potential errors.

Remember, while most T-SQL statements do not require a semicolon as a statement terminator, there are still some commands where its usage is mandatory. Being familiar with the syntax of these commands is important in order to use semicolons correctly and avoid any issues.