CTEs in Production SQL: What Actually Works
Common Table Expressions are one of those features every DBA tells you about on day one and then nobody really thinks about again until something explodes at 2am. I learned this properly after going through Joe Celko's books, trying his tree pattern approaches, and then watching my own queries fail in ways that weren't in any textbook. This is a breakdown of how table expressions actually behave in stored procedures, what recursion does to your execution plans, and how to use multiple CTEs without making your code unreadable or your server suffer. Joe Celko wrote extensively on recursive queries and hierarchical data. His approach to tree structures, especially the nested set model and closure tables, changed how a lot of people think about organizational charts and category hierarchies. But Celko's patterns are not always the fastest in practice. I spent months benchmarking his recursive CTE examples against materialized path approaches and found that for anything beyond read-heavy reporting, the recursive CTE was a liability. That doesn't mean it's useless. It means you need to understand what the optimizer actually does with it. A common table expression is defined by a WITH clause that sits before a SELECT, INSERT, UPDATE, or DELETE statement. You can chain multiple CTEs together by separating them with commas. The results of each CTE become a temporary named result set that exists only for the duration of that single statement. After the query finishes, the CTE is gone. There is no persistence. There is no indexing. There is no statistics collection. This matters more than most tutorials admit.
I ran into a problem once where a stored procedure used three chained CTEs to build a sales rollup across a date hierarchy. The first CTE pulled raw transactions, the second aggregated by region, and the third pivoted the results into monthly columns. The query worked fine on a development server with two million rows. On production with sixty million, it started timing out at twelve minutes. The issue wasn't the logic. It was the optimizer's estimate on the intermediate CTE results. SQL Server treats each CTE as an inline view but sometimes decides to materialize it and sometimes doesn't, depending on cost estimates. When it materializes a CTE that returns millions of rows, you're paying the cost of a temp table creation without any of the benefits of a proper indexed temporary structure. The workaround was straightforward but required a structural change. I replaced the middle CTE with a temporary table, populated it, and added a nonclustered index on the region column. The same query dropped to under thirty seconds. Not because the data changed. Because the optimizer finally had something it could estimate accurately. Recursion in CTEs uses a specific syntax. You define an anchor member and a recursive member joined by UNION ALL. The engine executes the anchor first, then feeds its output back into the recursive member until no new rows are produced. There is a safety valve called MAXRECURSION that caps the iterations. The default is one hundred. If your hierarchy goes deeper than that, your query fails with error 530. You can override this with the OPTION clause. I once inherited a stored procedure that crashed every Tuesday because a supplier relationship chain hit one hundred and twelve levels. The fix was setting MAXRECURSION to zero, which removes the limit entirely, combined with adding a cycle detection column that flagged paths longer than two hundred nodes. The actual data rarely exceeded fifty levels. The outliers were data quality issues that needed a separate cleanup job. The CTE itself was fine once it stopped bailing out. Nesting CTEs inside other CTEs is possible but the behavior is version-dependent. In SQL Server 2005 and later, you can reference a previously defined CTE from within a subsequent CTE in the same WITH block. You cannot nest a CTE inside another CTE definition the way you might nest subqueries. Some people try and get syntax errors they can't immediately parse. The pattern that works looks like this: you define CTE_A, then CTE_B references CTE_A, then CTE_C references CTE_B, and the final query references CTE_C. The execution plan treats each layer as a separate step in the pipeline. Readability degrades quickly past three levels. I stop at two and use a temporary table for the third stage if I need it.
Multiple CTEs in a single WITH clause are more common than nested ones and more frequently misused. Each CTE can only be referenced once in the outer query. If you need to use the same CTE result in two different places, you have to redefine it or wrap it in a subquery. This restriction exists because CTEs are not objects. They are scope-bound name bindings for expressions. You cannot SELECT from a CTE twice in the same batch. I've seen developers work around this by calling the same CTE name twice and getting a confusing error about undefined names, which only happens because the second reference is treated as a completely separate scope that has no knowledge of the first definition. The pattern to follow is to evaluate each CTE once, assign its result to a table variable or temp table if you need reuse, and keep the CTE layer minimal. Performance with CTEs and stored procedures has a few counter-intuitive aspects. First, CTEs do not cache. Every execution rebuilds the entire expression tree. If your stored procedure runs the same CTE three times, even with identical parameters, the optimizer generates three separate plans for three separate invocations. Second, CTEs participate in parameter sniffing just like any other inline construct. If your first execution with large parameters generates a plan that works well for large datasets, subsequent executions with small parameters may still use that plan and perform poorly. Third, the recursive CTE anchor and recursive members are evaluated in a specific order that the optimizer can sometimes reorder, and when it does, you can get surprising performance differences between otherwise identical queries. I benchmarked a hierarchical query where swapping the order of UNION ALL members changed the execution time by forty percent because the optimizer chose a different join strategy on the recursive loop. When recursion depth is the bottleneck, materialized path or nested set models often outperform recursive CTEs. The nested set approach stores left and right values for each node and answers hierarchy questions with simple range queries. It requires updates when the tree changes, which is the tradeoff. For systems where hierarchy changes are rare, nested set is significantly faster for reads. For systems with frequent tree modifications, the closure table pattern strikes a better balance. It maintains a separate mapping table of all ancestor-descendant pairs and updates that table on inserts and deletes. Query performance is equivalent to nested set. Maintenance overhead is manageable if you keep the tree updates transactional.
Get the Full Details
I encountered a specific edge case where multiple CTEs interacted with a recursive query in an unexpected way. The procedure had a non-recursive CTE that filtered a large events table, followed by a recursive CTE that walked a category tree, followed by a final CTE that joined the two. The join between the filtered events and the category hierarchy produced a cross join effect because the optimizer estimated the recursive CTE returned far fewer rows than it actually did. The cardinality estimate was off by a factor of eight hundred. The query planner chose a nested loops join with the recursive CTE as the inner input, which meant for every event row it re-executed the entire recursion. Adding an OPTION RECOMPILE hint eliminated the stale estimate but introduced plan cache bloat. The real fix was adding a STATS UPDATE on the recursive member's output column before the final query block. This forced accurate estimates without the recompilation overhead. The difference between the broken and fixed version was fourteen minutes versus nine seconds. There are scenarios where CTEs simply will not help you. If you need to iterate over a cursor-like result set and conditionally branch based on intermediate calculations, a CTE is the wrong tool. You would be better served by a temporary table with explicit indexed lookups or a table-valued function with parameters. Recursive CTEs also struggle with graphs that contain cycles, not just hierarchical trees. A BOM explosion with circular references will enter an infinite loop unless you explicitly track visited nodes. I wrote a cycle detection approach using a string accumulator that recorded the path taken so far and checked each new node against it. It added overhead but prevented the recursion from hanging the session. The MAXRECURSION limit catches this eventually, but it throws an error instead of handling it gracefully, which is worse for stored procedure callers expecting a result set. The core principles that hold up across all these patterns are straightforward. Keep CTEs short and single-purpose. Do not chain more than two layers deep inside a single WITH block. Use temporary tables when you need reuse or when the optimizer's estimates are unreliable. Understand that recursive CTEs are iterative in disguise and that iteration has a cost that grows linearly with depth and branching factor. Test with MAXRECURSION set to a realistic ceiling during development so you catch unbounded recursion before it hits production. And when performance becomes an issue, measure the actual execution plan rather than guessing whether the CTE is the problem or whether the underlying table statistics are stale.
I have not found a single authoritative resource that covers all of these interactions together. Celko's work is deep on recursion but light on modern optimizer behavior. The official documentation covers syntax thoroughly but barely touches on when not to use the feature. The practical knowledge comes from watching queries fail under load and tracing back to the CTE structure that caused it. The table expressions patterns described here are the ones that survived repeated production failures. They are not elegant. They are not always the shortest solution. They are the ones that kept running when everything else broke.
