Let me be honest. When I first encountered CTEs, I thought they were just a fancy way to write subqueries. Why bother? Subqueries worked fine. Then I had to write a hierarchy report for an employee table with managers and direct reports. After three hours of nested subqueries that made no sense even to me, I discovered recursive CTEs. Everything changed.
So let’s talk about CTEs. What they are, why they matter, and how you can use them without pulling your hair out
What Is a CTE Exactly?
CTE stands for Common Table Expression. It’s a temporary result set you define at the top of your query and reference below, kind of like a derived table but cleaner. You write it once, you use it wherever you want in that query.
Here’s the basic syntax:
That’s it. You define the CTE, give it a name, and reference it like a table. The query engine processes it once and keeps it available for the rest of your statement.
But the real magic starts when you need to reference the CTE multiple times in the same query.
The Day You Actually Need a CTE
Imagine you’re building a sales dashboard. You need to calculate monthly revenue, month-over-month growth, and the percentage each product contributes to total sales. Without a CTE, you’d write the same aggregation three times or use a temp table.
With a CTE, you aggregate once and reference it everywhere:
This reads like a story. First, you define what monthly sales look like. Then you ask questions about that data. It’s clear, maintainable, and anyone reading your code understands what’s happening.
Recursive CTEs: The Game Changer
Here’s where things get interesting. A recursive CTE references itself. It’s designed for hierarchical data—org charts, category trees, bill of materials, any data where one row points to another in the same table.
Let me show you the pattern:
The anchor part picks the root. The recursive part joins back to the CTE to find children. Each iteration goes one level deeper until there are no more rows to find.
This is the cleanest way to traverse a hierarchy in SQL. No loops. No cursors. Just a simple, elegant query.
When Recursion Goes Wrong
Recursive CTEs have a dark side. If you accidentally create an infinite loop, SQL Server will stop at 100 recursions by default. That’s a safety net, not a solution.
The trick is making sure your data has no cycles. If Employee A reports to Employee B and Employee B reports to Employee A, your recursive CTE will loop forever. Always check your data before writing recursive queries.
You can also control the recursion depth:
This caps the depth at 10 levels. If you hit that limit, SQL Server throws an error instead of crashing. It’s a good habit.
The Performance Tradeoff
CTEs aren’t always faster than subqueries or temp tables. The query optimizer treats them similarly, but there are subtle differences.
For non-recursive CTEs, performance is usually identical to derived tables. The benefit is readability, not speed.
For recursive CTEs, performance depends entirely on your data shape. A well-indexed hierarchy with a small depth will be fast. A wide tree with thousands of nodes and poor indexes will crawl. The recursive part executes repeatedly, so your join conditions need proper indexes.
Naming Matters
One thing that trips people up. CTE names must be unique in your query. You can define multiple CTEs by separating them with commas:
But you can’t reference a CTE inside another CTE’s definition unless they’re in order. The first one knows nothing about the second.
When to Reach for a CTE
I use CTEs when my query has multiple references to the same aggregated data, when I’m writing recursive hierarchy queries, or simply when I want my SQL to be readable six months from now.
I don’t use them for simple subqueries. That’s overkill. And I avoid recursive CTEs on huge datasets without testing first.
One More Example Before We Wrap
Here’s a practical pattern I use almost weekly. Finding duplicate records with a CTE:
This tells me all duplicate emails, but only returns the earliest one. Clean, concise, and it works every time.
Final Thought
CTEs won’t make you a better SQL developer overnight. But they’ll make your code easier to write, easier to debug, and easier for the next person to understand. And honestly, that’s worth more than any performance trick you’ll ever learn. Start with a simple one tomorrow. You’ll see what I mean.
Author By
