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
DELETEdirectly 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
