Why Use a Semicolon Before a Common Table Expression (CTE) in SQL Server?

Semicolon in Common Table Expression (CTE) Statements

In SQL Server, Common Table Expression (CTE) statements often include a semicolon before the statement, as seen in the example below:

;WITH OrderedOrders AS --semicolon here
(
    SELECT SalesOrderID, OrderDate,
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
    FROM Sales.SalesOrderHeader 
) 
SELECT * 
FROM OrderedOrders 
WHERE RowNumber BETWEEN 50 AND 60

This semicolon serves two main purposes:

• Avoiding Ambiguity: The WITH keyword can be used in other contexts, such as:

..FROM..WITH (NOLOCK)..
RESTORE..WITH MOVE..

To prevent confusion, a semicolon is used to terminate the statement before WITH to differentiate it from other uses.

• Optional Statement Termination: In SQL Server, semicolons are optional for statement termination. However, it is generally recommended to use semicolons to improve readability and avoid errors.

When used together, these factors dictate that the CTE statement should be terminated before the WITH clause to avoid ambiguities. Therefore, the semicolon is inserted before WITH to ensure proper parsing of the statement.

This rule also applies to the MERGE command, which has a similar requirement for a semicolon before the statement.

The above is the detailed content of Why Use a Semicolon Before a Common Table Expression (CTE) in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!