Many engineers believe Common Table Expressions (CTEs) are always faster than subqueries.
In modern SQL Server (and PostgreSQL), that is a myth.
Here is what actually happens under the hood:
1.
Inlining & The Query Optimizer By default, the SQL optimizer treats standard CTEs and derived tables (subqueries) almost identically: The engine expands both into the same relational tree.
They generate the exact same execution plan and I/O cost.
2.
When CTEs Truly Win: Readability & Pipeline Stacking: You can chain 5 CTEs sequentially without deeply nested pyramid brackets.
In-Place Deduplication: In SQL Server, you can run directly on a CTE, and it deletes duplicate rows straight from the real underlying table!
3.
The Big Trap (Spooling Overhead): If you reference the same CTE multiple times in a query (e.g. ), SQL Server may execute the underlying CTE query multiple times or create a Lazy Spool in . -> Fix: For heavy multi-million row reuse, use a Temporary Table () with an explicit Clustered Index instead! 💡 How do you choose between CTEs, Temp Tables, and Subqueries in your pipelines? 💼 Connect on LinkedIn: linkedin.com/in/arpitmbangre