Common Table Expression, why semicolon?

SqlSql ServerTsqlSql Server-2008Common Table-Expression

Sql Problem Overview


Usually in SQL Server Common Table Expression clause there is semicolon in front of the statement, like this:

;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

Why?

Sql Solutions


Solution 1 - Sql

  • To avoid ambiguity because WITH can be used elsewhere
    ..FROM..WITH (NOLOCK)..
    RESTORE..WITH MOVE..
  • It's optional to terminate statements with ; in SQL Server

Put together, the previous statement must be terminated before a WITH/CTE. To avoid errors, most folk use ;WITH because we don't know what is before the CTE

So

DECLARE @foo int;

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

is the same as

DECLARE @foo int

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

The MERGE command has a similar requirement.

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionjraraView Question on Stackoverflow
Solution 1 - SqlgbnView Answer on Stackoverflow