Common Table Expression, why semicolon?
12,514
- 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.
Author by
jrara
Updated on June 17, 2022Comments
-
jrara almost 2 years
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?
-
Andriy M over 12 yearsA 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...
-
gotqn about 9 yearsFor 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.
-
Manngo about 4 yearsMicrosoft 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.
-
ZygD about 4 years@Manngo agree, but sometimes it's just defensive programming especially in a shared dev environment