sql-server

How is “with” being interpreted in SQL Server?

Microsoft recommend ending SQL statements with a semicolon.

Here are two statements where this is important:

SELECT * FROM customers;

WITH cte AS (
    SELECT * FROM customers
)   --  oops
SELECT * FROM cte;

Anybody who’s used CTEs knows that without the semicolon you’ll get an error.

Without the semicolon, SQL Server normally tries to guess where the next statement starts, but that backfires here. Apparently it thinks that the WITH keyword continues from the previous statement.

Is SQL interpreting the WITH as something else? What would that be?

Solution

  • The problem with WITH is that it's part of many other clauses, making it harder to parse correctly.

    For example:

    SELECT *
FROM sometable
WITH (NOLOCK)

SELECT *
FROM OPENJSON(col)
WITH (...)

RAISERROR('Error', 11, 11) WITH NOWAIT

    In those, it can be hard to figure out if WITH belongs to the previous statement or to a beginning of a new CTE statement.

    Of course, it's probably possible to make parser figure things out, but it's not always easy, and i'm guessing SQL Server team made the decision to make ; obligatory, same way it was done with: MERGE and THROW clauses.