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:

1. Readability & Pipeline Stacking: You can chain 5 CTEs sequentially without deeply nested pyramid brackets.
2. 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