Subqueries vs CTEs: Query Optimizer Internals & Memory Spooling Explained

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 . -- Pattern A: Derived Table (Subquery) SELECT DeptID , EmpName , Salary FROM ( SELECT DeptID , EmpName , Salary , DENSE_RANK () OVER ( PARTITION BY DeptID ORDER BY Salary DESC ) AS rnk FROM Employees ) RankedData WHERE rnk <= 2 ; -- Pattern B: Common Table Expression (CTE) WITH RankedData AS ( SELECT DeptID , EmpName , Salary , DENSE_RANK () OVER ( PARTITION BY DeptID ORDER BY Salary DESC ) AS rnk FROM Employees ) SELECT DeptID , EmpName , Salary FROM RankedData WHERE rnk <= 2 ; 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 DELETE directly on a CTE, and it deletes duplicate rows straight from the real underlying table! WITH DuplicateCleaner AS ( SELECT CustomerID , Email , ROW_NUMBER () OVER ( PARTITION BY Email ORDER BY RegistrationDate ASC ) AS rn FROM Customers WHERE Email IS NOT NULL ) DELETE FROM DuplicateCleaner WHERE rn > 1 ; -- ✅ Clean in-place deletion! 3. The Big Trap (Spooling Overhead): If you reference the same CTE multiple times in a query (e.g. CTE_A JOIN CTE_A ), SQL Server may execute the underlying CTE query multiple times or create a Lazy Spool in tempdb . -> Fix: For heavy multi-million row reuse, use a Temporary Table ( #TempTable ) 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