Execution Plan Analysis: CTEs vs Temp Tables vs Derived Tables

Many SQL developers choose CTEs, Temp Tables, or Derived Tables based on syntax or readability – but the execution plan tells the real story.

This article explains:

• How SQL Server generates execution plans for each
• Why CTEs and Derived Tables often behave the same
• When Temp Tables change the plan dramatically
• How to read execution plans correctly
• Real examples with operator-level analysis

1. What Is an Execution Plan (Quick Refresher)

An execution plan shows:

• How SQL Server accesses data
• Join methods (Nested Loop, Hash Match, Merge)
• Index usage (Index Seek, Index Scan)
• Estimated vs actual rows
• Memory grants and spills

Key point:
SQL Server optimizes the final query, not how you wrote it.

2. Execution Plan for CTEs

Example CTE

WITH SalesCTE AS (
    SELECT CustomerID, SUM(Amount) AS TotalSales
    FROM Sales
    GROUP BY CustomerID
)
SELECT *
FROM SalesCTE
WHERE TotalSales > 100000;

What the Execution Plan Shows

• No separate operator for SalesCTE
• The CTE is expanded (inlined) into the main query
• Operators typically include:
  • Index Scan / Seek
  • Stream Aggregate
  • Filter

Important

A CTE is NOT executed independently.
It behaves like query text substitution.

Performance Trap

If the CTE is referenced multiple times:

WITH BigCTE AS (
    SELECT * FROM Orders WHERE OrderDate >= '2024-01-01'
)
SELECT COUNT(*) FROM BigCTE;
SELECT SUM(Amount) FROM BigCTE;

Execution plan shows two separate scans of Orders.

3. Execution Plan for Derived Tables

Example Derived Table

SELECT *
FROM (
    SELECT CustomerID, SUM(Amount) AS TotalSales
    FROM Sales
    GROUP BY CustomerID
) AS DT
WHERE TotalSales > 100000;

Execution Plan Reality

• Almost identical to the CTE plan
• Same operators
• Same join strategy
• Same estimated cost

Key Insight

Derived tables and CTEs usually produce the same execution plan.

Difference Is:

• Readability
• Reusability
  – not execution behavior.

4. Execution Plan for Temp Tables (Big Difference)

Example Temp Table

SELECT CustomerID, SUM(Amount) AS TotalSales
INTO #SalesTemp
FROM Sales
GROUP BY CustomerID;

SELECT *
FROM #SalesTemp
WHERE TotalSales > 100000;

Execution Plan Characteristics

You now get two plans:

Plan 1: Populate Temp Table

• Index Scan / Seek on Sales
• Aggregate
• Insert into #SalesTemp

Plan 2: Query Temp Table

• Clustered Index Scan (or Seek if indexed)
• Accurate row estimates
• Possible index usage

Temp tables introduce a physical boundary

5. Statistics & Cardinality Estimation (Huge Factor)

CTE / Derived Table

• No statistics
• Optimizer guesses row counts
• Can cause bad join choices

Temp Table

• Statistics automatically created
• Accurate row estimates
• Better join algorithms

This is why temp tables often fix bad execution plans.

6. Join Strategy Differences (Very Important)

Scenario: Large Dataset Join

Using CTE

WITH BigOrders AS (
    SELECT * FROM Orders WHERE OrderDate >= '2024-01-01'
)
SELECT *
FROM BigOrders o
JOIN Customers c ON o.CustomerID = c.CustomerID;

Execution Plan:

• Nested Loop (bad for large data)
• Underestimated rows

Using Temp Table

SELECT *
INTO #BigOrders
FROM Orders
WHERE OrderDate >= '2024-01-01';

SELECT *
FROM #BigOrders o
JOIN Customers c ON o.CustomerID = c.CustomerID;

Execution Plan:

• Hash Match or Merge Join
• Accurate memory grant
• Better performance

7. Memory Grants & Spills

CTE / Derived Table

• Memory grant based on estimates
• Underestimation → TempDB spills
• Overestimation → Wasted memory

Temp Table

• Memory grant based on real statistics
• Far fewer spills
• Predictable behavior

8. Actual vs Estimated Rows (Red Flag Area)

Pattern	What You See
CTE / Derived	Estimated: 1 row, Actual: 1,000,000
Temp Table	Estimated ≈ Actual

Always check:

• Actual Execution Plan
• Operator warnings (⚠️ yellow triangles)

9 Execution Plan Reuse & Recompilation

CTE / Derived Table

• Plan cached with query
• Can suffer from parameter sniffing

Temp Table

• Often triggers recompilation
• Fresh statistics
• More stable plans

In reporting & ETL workloads, this is a big advantage.

10. Execution Plan Comparison Summary

Feature	CTE	Derived Table	Temp Table
Separate plan node	NO	NO	YES
Statistics	NO	NO	YES
Accurate estimates	NO	NO	YES
Reuse cost	High	High	Low
Debuggable	NO	NO	YES

11. Golden Rules from Execution Plans

If execution plan shows bad row estimates → use Temp Table
If query is simple and single-use → CTE / Derived
If plan has memory spills or nested loops on large data → Temp Table
If readability matters → CTE
If performance matters → Temp Table

Final Verdict (Based on Execution Plans)

CTEs & Derived Tables are logical constructs
Temp Tables are physical optimization tools

Think Like the Optimizer

• CTE ≈ Derived Table ≈ Inline View
• Temp Table = Optimization boundary