All 7 Common Table Expressions (CTEs) questions
More in Common Table Expressions (CTEs)
What is a common table expression, and why would you use one instead of a subquery? How do you define multiple common table expressions within a single query? What is a recursive common table expression, and what kind of problems is it commonly used to solve? How do you prevent a recursive common table expression from running forever if the underlying data accidentally contains a circular reference? How would you use a CTE combined with a window function to identify and remove duplicate rows from a table? Can a common table expression be used inside an UPDATE, DELETE, or MERGE statement, and how does that work?
Advertisement
SQL Server › Common Table Expressions (CTEs)

What are the main limitations of common table expressions that developers should be aware of?

Intermediate

Answer

A CTE only exists for the duration of the single statement that immediately follows its definition and cannot be reused across multiple separate statements, it cannot include an ORDER BY clause except when combined with TOP, and unlike a temporary table, it does not have its own indexes, meaning very complex or frequently reused CTEs might sometimes perform better as an actual temporary table instead.
-- A CTE cannot be reused in a second, separate statement
WITH RecentOrders AS (SELECT * FROM Orders WHERE OrderDate >= '2026-01-01')
SELECT * FROM RecentOrders; -- valid
-- SELECT * FROM RecentOrders; -- this second reference would fail
Real-world example A developer initially tries referencing the same CTE across two separate SELECT statements, discovers this is not allowed, and instead stores the intermediate result in a temporary table when it needs to be reused multiple times.

Common follow-ups: When would a temporary table be a better choice than a CTE for performance reasons?;Why can't a CTE have its own indexes like a temporary table can?

Temporary Tables & Table Variables;Query Optimization & Plans
All 7 Common Table Expressions (CTEs) questions