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