Common Table Expression, Why Semicolon

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.

CTE and closing semi colons

A CTE needs only one WITH clause. You can create stacked CTE by just adding comma at the end of previous CTE followed by CTE name

;WITH nodes(node, node_name, parent, parent_name) AS
(
....
),Hierarchy(node, node_name, depth, parent, parent_name)
AS
(
..
)
SELECT *
FROM   Hierarchy
OPTION (MAXRECURSION 32767)

Understanding CTE Semicolon Placement

The answer you ask for was given in a comment already: This has nothing to do with the semicolon's placement.

Important: The CTE's WITH cannot follow right after a statement without an ending semicolon. There are many statments, where a WITH-clause would add something to the end of the statement (query hints, the WITH after OPENJSON etc.). The engine would have to guess, whether this WITH adds to the statment before or if it is a CTE's start. That's the reason, why we often see

;WITH cte AS (...)

That's actually the wrong usage of a semicolon. People put it there, just not to forget about it. Anyway it is seen as better style and best practice to end T-SQL statements always with a semicolon (and do not use ;WITH, as it adds an empty statement actually).

A CTE is not much more than syntactical sugar. Putting the CTE's code within a FROM(SELECT ...) AS SomeAlias would be roughly the same. In most cases this would lead to the same execution plan. It helps in cases, where you'd have to write the same FROM(SELECT ) AS SomeAlias in multiple places. And - in general - it makes things easier to read and understand. But it is not - by any means - comparable to a temp table or a table variable. The engine will treat it as inline code and you can use it in the same statement exclusively.

So this is the same:

WITH SomeCTE AS(...some query here...)
SELECT SomeCTE.* FROM SomeCTE;


SELECT SomeAlias.* 
FROM (...some query here...) AS SomeAlias;

Your example looks like you think of the CTE as kind of a temp table definition, which you can use in the following statements. But this is not correct.

After the CTE the engine expects another CTE or a final statement like SELECT or UPDATE.

WITH SomeCTE AS(...some query here...)
SELECT * FROM SomeCTE;

or

WITH SomeCTE AS( ...query... )
    ,AnotherCTE AS ( ...query... )
SELECT * FROM AnotherCTE;

...or another content added with the WITH clause:

WITH XMLNAMESPACES( ...namespace declarations...)
    ,SomeCTE AS( ...query... )
SELECT * FROM SomeCTE;

All of these examples are one single statement.

Putting a DECLARE @Something in the middle, would break this concept.

;' at the beginning of TSQL statements

It is supposed to be after the statements not before them. But in most cases in TSQL the terminating semi colons on statements are currently optional in practice (though technically Not ending Transact-SQL statements with a semicolon is deprecated) and the presence of statement terminating semi colons is not enforced.

An exception is the MERGE statement (that does require a terminating semi colon) and also statements preceding WITH or THROW

So this is a somewhat defensive practice for answers on StackOverflow in case the OP (or future readers) pastes it into the middle of some existing batch that doesn't have the required semi colon on the preceding statement and then complains it doesn't work and they receive the following error.

Incorrect syntax near the keyword 'with'. If this statement is a
common table expression, an xmlnamespaces clause or a change tracking
context clause, the previous statement must be terminated with a
semicolon.

In the case that the preceding statement is terminated with a semicolon the additional one does no harm. It just is treated as an empty statement.

This practice can itself cause problems though where the CTE is used in a context where multi statements are not valid. e.g. Inserting a semicolon before the WITH here would break it.

CREATE VIEW V1
AS
  WITH T(X)
       AS (SELECT 1)
  SELECT *
  FROM   T; 

Similarly for THROW blindly inserting a leading semi colon can cause problems too.

IF @i IS NULL
;THROW 50000, '@i IS NULL', 1;  

Incorrect syntax near ';'.

I have fixed the example you give in your question and changed it to

; 

--Ensure that any immediately preceding statement is terminated with a semicolon above
WITH cte
     AS (SELECT ROW_NUMBER() OVER (PARTITION BY Col1, Col2, Col3 
                                       ORDER BY ( SELECT 0)) RN
         FROM   #MyTable)
DELETE FROM cte
WHERE  RN > 1;

Incorrect syntax near the keyword 'with'...previous statement must be terminated with a semicolon

Use a comma to separate CTEs

;WITH SomeClause1 AS
(
  SELECT ....
)
, SomeClause2 AS
(
  SELECT ....
)

common table expression with insert select

The syntax in the question is actually correct. a semi colon was missing to finish the previous expression.

When should I use semicolons in SQL Server?

From a SQLServerCentral.Com article by Ken Powers:

The Semicolon

The semicolon character is a statement terminator. It is a part of the ANSI SQL-92 standard, but was never used within Transact-SQL. Indeed, it was possible to code T-SQL for years without ever encountering a semicolon.

Usage

There are two situations in which you must use the semicolon. The first situation is where you use a Common Table Expression (CTE), and the CTE is not the first statement in the batch. The second is where you issue a Service Broker statement and the Service Broker statement is not the first statement in the batch.

In SQL Server, when should you use GO and when should you use semi-colon ;?

GO only relates to SSMS - it isn't actual Transact SQL, it just tells SSMS to send the SQL statements between each GO in individual batches sequentially.

The ; is a SQL statement delimiter, but for the most part the engine can interpret where your statements are broken up.

The main exception, and place where the ; is used most often is before a Common Table Expression Statement.

Why does this SQL Server statement start with a semicolon (;)?

It's used to end the previous statement or batch of statements in there is any, so it is really part of the previous statement.

If the with is the first statement it is not needed. It's often included in the with by habit I guess, even when it's not needed. I tend to do this too just to be sure the previous statements are ended, but the correct use of the semi-colon would be to use it at the end of the previous statement (where it belongs).

The documentation for common table expressions states that:

When a CTE is used in a statement that is part of a batch, the
statement before it must be followed by a semicolon.

Some useful documentation:

MSDN: WITH common_table_expression (Transact-SQL)

---

Related Topics