Why Use a Semicolon Before a CTE in SQL Server?

CTE (Common Table Expression) and the Semicolon: Why is it Used?

In SQL Server, CTE (Common Table Expression) statements typically have a semicolon before the expression. This is not a requirement for statement termination in SQL Server, so why is it used with CTEs?

One reason is to avoid ambiguity. WITH can be used in other contexts besides CTEs, such as:

• FROM ..WITH (NOLOCK) for controlling locking behavior
• RESTORE ..WITH MOVE for restoring databases

Using a semicolon before the CTE ensures that it is not mistaken for one of these other contexts.

Furthermore, although statements in SQL Server can optionally be terminated with a semicolon, it is a best practice to consistently use them. This prevents errors that may occur when statements are not properly terminated.

For CTEs in particular, using a semicolon before the expression ensures that any preceding statements are properly terminated. For example:

DECLARE @foo int;

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

is equivalent to:

DECLARE @foo int

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

By using a semicolon before the CTE expression, it is clear that the preceding DECLARE statement is terminated.

Similarly, the MERGE command requires a semicolon before the MERGE statement, even though it is not a CTE. This again prevents ambiguity and ensures proper statement termination.

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