Common Table Expression, why semicolon?
- 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.