Why Use Semicolons Before Common Table Expressions (CTEs) in SQL Server?

Semicolons in SQL Server Common Table Expressions (CTEs)

In SQL Server, Common Table Expressions (CTEs) often include a semicolon in front of the statement, as illustrated below:

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

Purpose of the Semicolon

The semicolon serves two purposes:

• Avoids Ambiguity: WITH can also be used in other contexts, such as:

  • ..FROM..WITH (NOLOCK)..
  • RESTORE..WITH MOVE..
• Optional Statement Termination: In SQL Server, statements can optionally be terminated with ;.

In the context of CTEs, it is recommended to use ;WITH to avoid potential conflicts. If a CTE is preceded by a statement that does not end with a semicolon, accidentally using WITH as part of that statement could lead to unexpected behavior.

Example

Consider the following statement:

DECLARE @foo int;

WITH OrderedOrders AS
(
    SELECT SalesOrderID, OrderDate,
...;

This statement is equivalent to:

DECLARE @foo int;

;WITH OrderedOrders AS
(
    SELECT SalesOrderID, OrderDate,
...;

Conclusion

Using ;WITH before CTE statements adds clarity and consistency to SQL Server queries. It helps avoid ambiguity and ensures that statements are properly terminated, minimizing the risk of errors.

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