67

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?

gotqn
  • 42,737
  • 46
  • 157
  • 243
jrara
  • 16,239
  • 33
  • 89
  • 120

1 Answers1

97
  • 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.

gbn
  • 422,506
  • 82
  • 585
  • 676
  • 1
    A quick and concise answer, very nice. Mine would take me either half a day long or half a mile page (slightly exaggerated, but not too much). The 'benefits' of speaking English as a second language... – Andriy M Aug 04 '11 at 08:27
  • 16
    For newer versions - `The SQL Server documentation indicates that not terminating T-SQL statements with a semicolon is a deprecated feature. This means that the long-term goal is to enforce use of the semicolon in a future version of the product.` - `Miscrosoft SQL Server 2012 T-SQL Fundamentals by Itzik Ben-Gan.` – gotqn Mar 28 '15 at 08:22
  • 1
    Microsoft have deprecated the missing semicolon for over a decade, promising to require it in “a future version”. Good practice is always to use a terminating semicolon, just like all other databases. Among other things, this would remove the need for the quirky leading semicolon in this case. – Manngo Feb 15 '20 at 22:53
  • 1
    @Manngo agree, but sometimes it's just defensive programming especially in a shared dev environment – gbn Feb 18 '20 at 06:29