Common Table Expressions Quiz: Why Did My Second Query Fail?

This Common Table Expressions Quiz is about a query that works once and then breaks. The code looks fine, and the first result comes back clean. Read the setup, pick your answer, and then run the script to check yourself.

The Quiz

A table holds sales for a few cities. You write a CTE named RegionTotal that adds up the sales for each city. The first SELECT reads from RegionTotal and shows every city. Right after it, a second SELECT reads from RegionTotal again, this time with a filter. Both statements run in the same batch.

What happens to the second SELECT?

A. It returns the filtered rows, because the CTE is still defined
B. It fails with error 208, invalid object name
C. It returns zero rows
D. It returns the saved result of the first SELECT without running the query again

Take a moment and pick one before you read on.

The Answer

The answer is B. SQL Server fails the second SELECT with error 208, and the first one still returns its rows.

A CTE isn’t an object that stays in the database. It is a name that exists for one statement, the one right after the WITH clause. When that statement ends, the name is gone. The second SELECT looks for RegionTotal and finds nothing.

Prove It

Here is the quiz as a script. It creates a small database called SqlQuizCommonTableExpressions, used only for this example, so run it on a test server.

IF DB_ID(N'SqlQuizCommonTableExpressions') IS NULL CREATE DATABASE SqlQuizCommonTableExpressions;
GO
USE SqlQuizCommonTableExpressions;
GO
DROP TABLE IF EXISTS dbo.QuizSale;
CREATE TABLE dbo.QuizSale
(
    SaleID int IDENTITY(1,1) PRIMARY KEY,
    Region nvarchar(20) NOT NULL,
    Amount decimal(10,2) NOT NULL
);
INSERT INTO dbo.QuizSale (Region, Amount)
VALUES (N'Denver', 120.00), (N'Denver', 80.00), (N'Austin', 200.00), (N'Boston', 50.00), (N'Boston', 75.00);
WITH RegionTotal AS
(
    SELECT Region, SUM(Amount) AS Total
    FROM dbo.QuizSale
    GROUP BY Region
)
SELECT Region, Total FROM RegionTotal ORDER BY Region;
SELECT Region, Total FROM RegionTotal WHERE Total > 150;

On SQL Server 2025, the first SELECT returned three rows.

The second SELECT failed. This is the text SSMS shows in the Messages tab. It is output, not code to run.

Msg 208, Level 16, State 1, Line 17
Invalid object name 'RegionTotal'.

Why the Other Answers Are Wrong

A is what most people expect, because the WITH clause sits right above both queries. The scope of a CTE stops at the end of the statement that uses it. Writing a second SELECT underneath doesn’t extend it.

C would mean SQL Server accepts the name and finds no data. That never happens here. The name doesn’t exist, so SQL Server raises an error instead of returning an empty result.

D treats a CTE as a saved result. It isn’t one. A CTE is a named piece of query text, and SQL Server expands it where the statement uses it. Nothing is stored between statements.

How to Reuse the Result

When two statements need the same totals, store them. A temp table keeps the result for the whole session. You can read it as many times as you like.

SELECT Region, SUM(Amount) AS Total INTO #RegionTotal FROM dbo.QuizSale GROUP BY Region;
SELECT Region, Total FROM #RegionTotal ORDER BY Region;
SELECT Region, Total FROM #RegionTotal WHERE Total > 150;
DROP TABLE #RegionTotal;

The second query returned Austin and Denver, both at 200.00. You can also repeat the WITH clause before each statement. That costs more typing, and the query runs again each time.

Two CTEs in One Statement

A single WITH clause can define several CTEs, and a later one can read an earlier one. Write WITH once, and put a comma between the definitions. This version finds the cities with more than 150 in sales.

WITH CityTotal AS
(
    SELECT Region, SUM(Amount) AS Total
    FROM dbo.QuizSale
    GROUP BY Region
),
BigCity AS
(
    SELECT Region, Total FROM CityTotal WHERE Total > 150
)
SELECT Region, Total FROM BigCity ORDER BY Region;

The query returned two rows, Austin and Denver, each with a total of 200.00. Each step has its own name, so the query reads from top to bottom. That is the real value of a CTE. It makes a long query easier to read, and it doesn’t store anything.

A CTE Can Lead a Delete

The statement after the WITH clause doesn’t have to be a SELECT. It can be an INSERT, UPDATE, DELETE or MERGE. That makes a CTE a clean way to remove duplicate rows. This script numbers the rows for each email and deletes every row after the first. It deletes data, so run it on the test database only.

DROP TABLE IF EXISTS dbo.QuizSignup;
CREATE TABLE dbo.QuizSignup
(
    SignupID int IDENTITY(1,1) PRIMARY KEY,
    Email nvarchar(40) NOT NULL
);
INSERT INTO dbo.QuizSignup (Email)
VALUES (N'avery@example.com'), (N'jordan@example.com'), (N'avery@example.com'), (N'avery@example.com');
WITH Ranked AS
(
    SELECT SignupID, ROW_NUMBER() OVER (PARTITION BY Email ORDER BY SignupID) AS Pick
    FROM dbo.QuizSignup
)
DELETE FROM Ranked WHERE Pick > 1;
SELECT SignupID, Email FROM dbo.QuizSignup ORDER BY SignupID;

The two extra copies of the first email are gone, and the oldest row stays. The same scope rule applies here. The CTE lives for the DELETE and nothing after it. Check the SELECT inside the CTE on its own before you run the delete.

The Missing Semicolon

The second gotcha catches people who write a CTE after another statement. SQL Server reads WITH as the start of a table hint when the line before it has no semicolon. Try it.

SELECT COUNT(*) AS SaleCount FROM dbo.QuizSale
WITH LargeSale AS (SELECT SaleID FROM dbo.QuizSale WHERE Amount > 100)
SELECT SaleID FROM LargeSale;

This is the text SSMS shows in the Messages tab. It is output, not code to run.

Msg 336, Level 15, State 1, Line 2
Incorrect syntax near 'LargeSale'. If this is intended to be a common table expression, you need to explicitly terminate the previous statement with a semi-colon.

The fix is one character. End the line before WITH with a semicolon. The batch then returned a count of 5 and the two sales above 100, with SaleID 1 and 3.

SELECT COUNT(*) AS SaleCount FROM dbo.QuizSale;
WITH LargeSale AS (SELECT SaleID FROM dbo.QuizSale WHERE Amount > 100)
SELECT SaleID FROM LargeSale;

The Recursion Limit

A recursive CTE calls itself, and SQL Server stops it after 100 levels. This query counts from 1 to 150, so it needs 150 levels.

WITH Counter AS
(
    SELECT 1 AS N
    UNION ALL
    SELECT N + 1 FROM Counter WHERE N < 150
)
SELECT COUNT(*) AS Numbers FROM Counter;

This is the text SSMS shows in the Messages tab. It is output, not code to run.

Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Raise the limit with a query hint on the final statement. With MAXRECURSION set to 200, the query returned 150.

WITH Counter AS
(
    SELECT 1 AS N
    UNION ALL
    SELECT N + 1 FROM Counter WHERE N < 150
)
SELECT COUNT(*) AS Numbers FROM Counter OPTION (MAXRECURSION 200);

Pick a limit that fits your data. A value of 0 removes the limit, and a bug in the query can then loop until you stop it.

What to Remember

A CTE lives for one statement. If a second statement needs the result, use a temp table, or write the CTE again. Treat the 100 level limit as a safety net, and raise it only when the data needs it. The error names the limit, so it is easy to find when it fires.

In my own scripts, I end every statement with a semicolon. It costs nothing, and it removes the Msg 336 trap for good. When you finish testing, remove the example database.

USE master;
GO
ALTER DATABASE SqlQuizCommonTableExpressions SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE SqlQuizCommonTableExpressions;

A CTE is not a saved result, it is a name that lasts one statement.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.