SQL WORLD: The RIGHT Way to Use CTEs in SQL Server: Performance, Recursion, and Pitfalls

Common Table Expressions (CTEs) are one of the most powerful yet misunderstood features in T-SQL. I've seen countless developers use CTEs in SQL Server without understanding their performance implications, leading to queries that are elegant but painfully slow. In this intermediate-level guide, I'll show you the right way to use CTEs, when they shine, when they fail, and how to avoid the common pitfalls that can tank your query performance.

CTEs (Common Table Expressions) are temporary named result sets that exist only within the execution scope of a single query. They make complex queries more readable and enable recursive operations that would otherwise be impossible. However, they're not a silver bullet—understanding when to use them versus temp tables, subqueries, or derived tables is crucial for writing efficient SQL code.

What Are CTEs and Why Should You Care?

A Common Table Expression (CTE) is defined using the WITH clause and acts like a named subquery that you can reference within your main query. Think of it as creating a temporary view that disappears after your query completes.

Here's the basic syntax:

WITH CTE_Name AS (
    -- Your query here
    SELECT columns
    FROM table
    WHERE conditions
)
SELECT *
FROM CTE_Name;

CTEs offer several advantages over traditional subqueries:

Improved Readability - Break complex queries into logical, named steps Recursion Support - The only native T-SQL way to write recursive queries Multiple References - Reference the same CTE multiple times without repeating code Maintenance Benefits - Easier to debug and modify complex query logic

However, CTEs also have limitations that many developers overlook, particularly around performance optimization and how SQL Server handles them internally.

CTE Fundamentals: Your First Working Examples

Let's start with practical examples using the WideWorldImporters database to understand CTE basics before diving into advanced topics.

Basic CTE Example

Here's a simple CTE that calculates customer order summaries:

USE WideWorldImporters;
GO

-- Basic CTE to summarize customer orders
WITH CustomerOrderSummary AS (
    SELECT 
        c.CustomerID,
        c.CustomerName,
        COUNT(o.OrderID) AS TotalOrders,
        SUM(ol.Quantity * ol.UnitPrice) AS TotalRevenue
    FROM Sales.Customers c
    INNER JOIN Sales.Orders o ON c.CustomerID = o.CustomerID
    INNER JOIN Sales.OrderLines ol ON o.OrderID = ol.OrderID
    WHERE o.OrderDate >= '2016-01-01'
    GROUP BY c.CustomerID, c.CustomerName
)
SELECT 
    CustomerName,
    TotalOrders,
    TotalRevenue,
    TotalRevenue / TotalOrders AS AvgOrderValue
FROM CustomerOrderSummary
WHERE TotalOrders > 10
ORDER BY TotalRevenue DESC;

This CTE makes the query more readable by separating the aggregation logic from the final calculation and filtering.

Multiple CTEs in a Single Query

One of CTE's strengths is chaining multiple CTEs together for complex operations:

-- Multiple CTEs for inventory analysis
WITH StockLevels AS (
    SELECT 
        si.StockItemID,
        si.StockItemName,
        sih.QuantityOnHand,
        si.RecommendedRetailPrice
    FROM Warehouse.StockItems si
    INNER JOIN Warehouse.StockItemHoldings sih ON si.StockItemID = sih.StockItemID
),
RecentSales AS (
    SELECT 
        ol.StockItemID,
        SUM(ol.Quantity) AS SoldLast30Days
    FROM Sales.OrderLines ol
    INNER JOIN Sales.Orders o ON ol.OrderID = o.OrderID
    WHERE o.OrderDate >= DATEADD(DAY, -30, '2016-05-31')
    GROUP BY ol.StockItemID
)
SELECT 
    sl.StockItemName,
    sl.QuantityOnHand,
    ISNULL(rs.SoldLast30Days, 0) AS SoldLast30Days,
    sl.QuantityOnHand - ISNULL(rs.SoldLast30Days, 0) AS ProjectedRemaining,
    sl.RecommendedRetailPrice * ISNULL(rs.SoldLast30Days, 0) AS Last30DaysRevenue
FROM StockLevels sl
LEFT JOIN RecentSales rs ON sl.StockItemID = rs.StockItemID
WHERE sl.QuantityOnHand > 0
ORDER BY ProjectedRemaining;

Notice how we define multiple CTEs separated by commas, making complex business logic much easier to understand and maintain.

Recursive CTEs: Hierarchical Data Made Simple

Recursive CTEs are where CTEs truly shine. They're the standard way to query hierarchical data like organizational charts, bill of materials, or category trees in SQL Server.

Understanding Recursive CTE Structure

A recursive CTE has two parts:

Anchor Member - The base case (non-recursive starting point)
Recursive Member - References the CTE itself to traverse the hierarchy

Here's the general pattern:

WITH RecursiveCTE AS (
    -- Anchor member (base case)
    SELECT columns
    FROM table
    WHERE starting_condition
    
    UNION ALL
    
    -- Recursive member (references itself)
    SELECT columns
    FROM table t
    INNER JOIN RecursiveCTE r ON t.parent = r.child
)
SELECT * FROM RecursiveCTE;

Real-World Recursive Example: Organization Hierarchy

Let's use the Application.People table to traverse the organizational hierarchy:

-- Recursive CTE to show reporting structure
WITH EmployeeHierarchy AS (
    -- Anchor: Start with top-level employees (no manager)
    SELECT 
        PersonID,
        FullName,
        LogonName,
        NULL AS ManagerID,
        CAST(FullName AS NVARCHAR(1000)) AS HierarchyPath,
        0 AS Level
    FROM Application.People
    WHERE LogonName IS NOT NULL 
      AND PersonID NOT IN (
          SELECT DISTINCT LastEditedBy 
          FROM Application.People 
          WHERE LastEditedBy != PersonID
      )
    
    UNION ALL
    
    -- Recursive: Find employees reporting to current level
    SELECT 
        p.PersonID,
        p.FullName,
        p.LogonName,
        p.LastEditedBy AS ManagerID,
        CAST(eh.HierarchyPath + ' > ' + p.FullName AS NVARCHAR(1000)),
        eh.Level + 1
    FROM Application.People p
    INNER JOIN EmployeeHierarchy eh ON p.LastEditedBy = eh.PersonID
    WHERE p.PersonID != p.LastEditedBy
      AND eh.Level < 10  -- Safety limit to prevent infinite loops
)
SELECT 
    PersonID,
    FullName,
    Level,
    HierarchyPath
FROM EmployeeHierarchy
ORDER BY Level, FullName;

Important note: Always include a recursion limiter (like Level < 10) to prevent infinite loops if your data has circular references.

Recursive CTE for Running Totals

Recursive CTEs can also solve sequential calculation problems:

-- Recursive CTE to calculate cumulative sales by date
WITH DailySales AS (
    SELECT 
        CAST(o.OrderDate AS DATE) AS SaleDate,
        SUM(ol.Quantity * ol.UnitPrice) AS DailyRevenue
    FROM Sales.Orders o
    INNER JOIN Sales.OrderLines ol ON o.OrderID = ol.OrderID
    WHERE o.OrderDate >= '2016-01-01' 
      AND o.OrderDate < '2016-01-31'
    GROUP BY CAST(o.OrderDate AS DATE)
),
RunningTotal AS (
    -- Anchor: First date
    SELECT 
        SaleDate,
        DailyRevenue,
        DailyRevenue AS RunningTotal,
        1 AS DayNumber
    FROM DailySales
    WHERE SaleDate = (SELECT MIN(SaleDate) FROM DailySales)
    
    UNION ALL
    
    -- Recursive: Add each subsequent day
    SELECT 
        ds.SaleDate,
        ds.DailyRevenue,
        rt.RunningTotal + ds.DailyRevenue,
        rt.DayNumber + 1
    FROM DailySales ds
    INNER JOIN RunningTotal rt ON ds.SaleDate = DATEADD(DAY, 1, rt.SaleDate)
)
SELECT 
    SaleDate,
    DailyRevenue,
    RunningTotal
FROM RunningTotal
ORDER BY SaleDate
OPTION (MAXRECURSION 100);

Note the OPTION (MAXRECURSION 100) hint—this sets the maximum recursion depth, preventing runaway queries.

CTE Performance: The Truth Nobody Tells You

Here's where things get controversial. Many developers treat CTEs as a performance optimization, but CTEs are not materialized—they're essentially inline views that SQL Server can expand into the outer query.

Critical Performance Facts About CTEs

CTEs Are Not Cached - Each reference to a CTE causes SQL Server to re-execute that CTE's logic. If you reference a CTE multiple times, you might be running the same expensive query multiple times.

No Automatic Indexing - Unlike temp tables, CTEs can't have indexes, statistics, or constraints.

Optimizer Limitations - SQL Server might not optimize CTE references as efficiently as you'd expect, especially with multiple references.

Let's demonstrate this with a performance comparison:

-- CTE approach (potentially inefficient with multiple references)
WITH ExpensiveCalculation AS (
    SELECT 
        c.CustomerID,
        c.CustomerName,
        COUNT(o.OrderID) AS OrderCount,
        SUM(ol.Quantity * ol.UnitPrice) AS TotalSpent
    FROM Sales.Customers c
    INNER JOIN Sales.Orders o ON c.CustomerID = o.CustomerID
    INNER JOIN Sales.OrderLines ol ON o.OrderID = ol.OrderID
    GROUP BY c.CustomerID, c.CustomerName
)
SELECT 
    ec1.CustomerName,
    ec1.TotalSpent,
    ec2.CustomerName AS ComparisonCustomer,
    ec2.TotalSpent AS ComparisonSpent
FROM ExpensiveCalculation ec1
CROSS JOIN ExpensiveCalculation ec2  -- CTE executed TWICE here!
WHERE ec1.CustomerID < 100
  AND ec2.CustomerID < 100
  AND ec1.CustomerID != ec2.CustomerID;

In this example, the CTE is referenced twice, potentially causing SQL Server to execute the expensive aggregation twice. Let's compare this to a temp table approach:

-- Temp table approach (more efficient for multiple references)
SELECT 
    c.CustomerID,
    c.CustomerName,
    COUNT(o.OrderID) AS OrderCount,
    SUM(ol.Quantity * ol.UnitPrice) AS TotalSpent
INTO #CustomerStats
FROM Sales.Customers c
INNER JOIN Sales.Orders o ON c.CustomerID = o.CustomerID
INNER JOIN Sales.OrderLines ol ON o.OrderID = ol.OrderID
GROUP BY c.CustomerID, c.CustomerName;

-- Create index on temp table
CREATE INDEX IX_CustomerStats_CustomerID ON #CustomerStats(CustomerID);

-- Now query efficiently
SELECT 
    cs1.CustomerName,
    cs1.TotalSpent,
    cs2.CustomerName AS ComparisonCustomer,
    cs2.TotalSpent AS ComparisonSpent
FROM #CustomerStats cs1
CROSS JOIN #CustomerStats cs2
WHERE cs1.CustomerID < 100
  AND cs2.CustomerID < 100
  AND cs1.CustomerID != cs2.CustomerID;

DROP TABLE #CustomerStats;

The temp table approach materializes the results once, can be indexed, and is queried multiple times efficiently.

CTE vs Temp Tables vs Derived Tables: When to Use What

Understanding when to use each approach is critical for intermediate developers:

Use CTEs When:

Query readability is the primary goal and performance is acceptable
You need recursive queries (only option for recursion in T-SQL)
The CTE is referenced only once in the outer query
The result set is small to medium (thousands of rows, not millions)
You're building one-time ad-hoc queries or reports

Use Temp Tables When:

The CTE would be referenced multiple times
You need to index the intermediate results
Working with large result sets (hundreds of thousands or millions of rows)
You need statistics for better execution plans
Processing happens in multiple steps with different operations

Use Derived Tables When:

Simple, single-reference scenarios
Inline filtering or aggregation
You want to avoid the overhead of temp table creation

Here's a practical comparison using supplier analysis:

-- CTE approach (good for readability)
WITH TopSuppliers AS (
    SELECT 
        s.SupplierID,
        s.SupplierName,
        COUNT(po.PurchaseOrderID) AS OrderCount
    FROM Purchasing.Suppliers s
    INNER JOIN Purchasing.PurchaseOrders po ON s.SupplierID = po.SupplierID
    GROUP BY s.SupplierID, s.SupplierName
    HAVING COUNT(po.PurchaseOrderID) > 10
)
SELECT * FROM TopSuppliers ORDER BY OrderCount DESC;

-- Derived table approach (more compact, same performance)
SELECT *
FROM (
    SELECT 
        s.SupplierID,
        s.SupplierName,
        COUNT(po.PurchaseOrderID) AS OrderCount
    FROM Purchasing.Suppliers s
    INNER JOIN Purchasing.PurchaseOrders po ON s.SupplierID = po.SupplierID
    GROUP BY s.SupplierID, s.SupplierName
    HAVING COUNT(po.PurchaseOrderID) > 10
) AS TopSuppliers
ORDER BY OrderCount DESC;

Both approaches generate identical execution plans since they're referenced once. Choose based on readability preferences.

Common CTE Pitfalls and How to Avoid Them

Pitfall 1: Infinite Recursion

Recursive CTEs without proper termination conditions can run forever (or until hitting MAXRECURSION):

-- DANGEROUS: No recursion limit
WITH BadRecursion AS (
    SELECT 1 AS Number
    UNION ALL
    SELECT Number + 1
    FROM BadRecursion
    WHERE Number < 1000000  -- This could run for a very long time
)
SELECT * FROM BadRecursion
OPTION (MAXRECURSION 0);  -- 0 means unlimited - DANGEROUS!

-- SAFE: Always set reasonable limits
WITH SafeRecursion AS (
    SELECT 1 AS Number
    UNION ALL
    SELECT Number + 1
    FROM BadRecursion
    WHERE Number < 100
)
SELECT * FROM SafeRecursion
OPTION (MAXRECURSION 100);  -- Safety limit

Best Practice: Always set MAXRECURSION to a reasonable value (default is 100, max is 32,767).

Pitfall 2: Over-Relying on CTEs for Performance

CTEs don't magically improve performance. In fact, they can hurt it:

-- Problematic: Large CTE referenced multiple times
WITH LargeOrderDetails AS (
    SELECT 
        o.OrderID,
        o.CustomerID,
        o.OrderDate,
        ol.StockItemID,
        ol.Quantity,
        ol.UnitPrice,
        si.StockItemName
    FROM Sales.Orders o
    INNER JOIN Sales.OrderLines ol ON o.OrderID = ol.OrderID
    INNER JOIN Warehouse.StockItems si ON ol.StockItemID = si.StockItemID
    WHERE o.OrderDate >= '2013-01-01'  -- Millions of rows
)
SELECT 
    lod1.CustomerID,
    COUNT(DISTINCT lod1.OrderID) AS OrderCount,
    COUNT(DISTINCT lod2.StockItemID) AS UniqueProducts
FROM LargeOrderDetails lod1
INNER JOIN LargeOrderDetails lod2 ON lod1.CustomerID = lod2.CustomerID
GROUP BY lod1.CustomerID;

Solution: Use a temp table with appropriate indexes instead.

Pitfall 3: Forgetting CTE Scope

CTEs only exist within a single statement. You can't reference them across multiple queries:

-- This FAILS
WITH CustomerList AS (
    SELECT CustomerID, CustomerName
    FROM Sales.Customers
)
SELECT * FROM CustomerList;  -- Works

SELECT * FROM CustomerList;  -- ERROR: Invalid object name 'CustomerList'

If you need to reference results multiple times across statements, use a temp table or table variable.

Pitfall 4: Not Understanding Optimizer Behavior

SQL Server might not optimize CTEs the way you expect:

-- You might expect this to filter before aggregation
WITH AllOrders AS (
    SELECT 
        CustomerID,
        OrderID,
        OrderDate
    FROM Sales.Orders
)
SELECT 
    CustomerID,
    COUNT(OrderID) AS OrderCount
FROM AllOrders
WHERE OrderDate >= '2016-01-01'  -- Filter after CTE
GROUP BY CustomerID;

-- More explicit and potentially better optimized
WITH FilteredOrders AS (
    SELECT 
        CustomerID,
        OrderID
    FROM Sales.Orders
    WHERE OrderDate >= '2016-01-01'  -- Filter inside CTE
)
SELECT 
    CustomerID,
    COUNT(OrderID) AS OrderCount
FROM FilteredOrders
GROUP BY CustomerID;

While SQL Server's optimizer should push predicates into CTEs, being explicit helps ensure optimal execution.

Advanced CTE Techniques

Technique 1: CTEs with UPDATE and DELETE

CTEs aren't just for SELECT statements:

-- Use CTE to update records based on complex logic
WITH OutdatedPrices AS (
    SELECT 
        si.StockItemID,
        si.UnitPrice,
        AVG(ol.UnitPrice) AS AvgSoldPrice
    FROM Warehouse.StockItems si
    INNER JOIN Sales.OrderLines ol ON si.StockItemID = ol.StockItemID
    INNER JOIN Sales.Orders o ON ol.OrderID = o.OrderID
    WHERE o.OrderDate >= DATEADD(MONTH, -3, '2016-05-31')
    GROUP BY si.StockItemID, si.UnitPrice
    HAVING si.UnitPrice < AVG(ol.UnitPrice) * 0.8
)
UPDATE si
SET si.UnitPrice = op.AvgSoldPrice
FROM Warehouse.StockItems si
INNER JOIN OutdatedPrices op ON si.StockItemID = op.StockItemID;

This pattern makes complex UPDATE logic much more readable.

Technique 2: CTEs for Dynamic Pivoting Logic

CTEs help structure complex pivoting operations:

-- Use CTE to prepare data for pivoting
WITH MonthlySales AS (
    SELECT 
        YEAR(o.OrderDate) AS OrderYear,
        MONTH(o.OrderDate) AS OrderMonth,
        SUM(ol.Quantity * ol.UnitPrice) AS MonthlyRevenue
    FROM Sales.Orders o
    INNER JOIN Sales.OrderLines ol ON o.OrderID = ol.OrderID
    WHERE o.OrderDate >= '2015-01-01' 
      AND o.OrderDate < '2016-01-01'
    GROUP BY YEAR(o.OrderDate), MONTH(o.OrderDate)
)
SELECT 
    OrderYear,
    [1] AS Jan, [2] AS Feb, [3] AS Mar, [4] AS Apr,
    [5] AS May, [6] AS Jun, [7] AS Jul, [8] AS Aug,
    [9] AS Sep, [10] AS Oct, [11] AS Nov, [12] AS Dec
FROM MonthlySales
PIVOT (
    SUM(MonthlyRevenue)
    FOR OrderMonth IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) AS PivotTable;

Best Practices and Expert Tips

Test Performance with Actual Execution Plans - Always compare CTE performance against alternatives using execution plans and IO statistics.

Name CTEs Descriptively - Use clear names like ActiveCustomersLast90Days instead of generic names like CTE1 or TempData.

Keep CTEs Focused - Each CTE should represent one logical step. Don't try to cram too much logic into a single CTE.

Document Complex Recursion - Add comments explaining the anchor, recursive logic, and termination conditions.

Consider Readability vs Performance Trade-offs - Sometimes a slightly slower CTE is worth it for maintainability, especially in reporting queries that run once per day.

Use Table Hints Sparingly - While you can add hints like WITH (NOLOCK) to CTE queries, use them carefully and document why they're necessary.

From my experience, the developers who excel with CTEs understand that they're primarily a readability tool that can have performance implications. Use them wisely, measure their impact, and don't be afraid to switch to temp tables when performance demands it.

Summary: Key Takeaways

CTEs in SQL Server are powerful tools for improving query readability and enabling recursive operations, but they require careful consideration:

CTEs are not materialized - They're inline views that don't cache results between references
Use CTEs for readability and single-reference scenarios, especially recursive queries
Switch to temp tables when referencing results multiple times or working with large datasets
Recursive CTEs require safety limits - Always set MAXRECURSION to prevent infinite loops
Multiple CTEs can be chained together to break complex logic into manageable steps
Test performance - Don't assume CTEs are faster or slower without measuring
CTEs work with UPDATE and DELETE - Not just SELECT statements
Consider your audience - More readable code is valuable even if it's slightly slower

The right way to use CTEs is to understand their strengths and limitations, choose the appropriate tool for each scenario, and always validate your decisions with performance testing.

Frequently Asked Questions

Q: Are CTEs faster than subqueries or temp tables?

A: Not necessarily. CTEs and subqueries often generate identical execution plans since CTEs are essentially named subqueries. Temp tables can be faster when you need to reference results multiple times, add indexes, or work with large datasets. The performance difference depends entirely on your specific scenario. Always test with actual execution plans and IO statistics—never assume one approach is universally faster.

Q: Can I use multiple recursive CTEs in one query?

A: Yes, but be cautious. You can define multiple CTEs in one query, and any of them can be recursive. However, each recursive CTE must follow the anchor-then-recursive-member pattern, and they can't reference each other recursively (you can't have mutually recursive CTEs). Also, the MAXRECURSION hint applies to all recursive CTEs in the query, so set it appropriately for the deepest recursion you expect.

Q: Why does my CTE seem to run slower than expected when I reference it multiple times?

A: Because SQL Server doesn't materialize CTE results. When you reference a CTE multiple times, SQL Server may execute that CTE's logic separately for each reference, essentially running the same query multiple times. This is one of the most common CTE pitfalls. If you need to reference the same result set multiple times, especially with expensive calculations or large datasets, use a temp table instead. You can test this by looking at the execution plan—if you see the same operations repeated, that's a sign to use a temp table.

The RIGHT Way to Use CTEs in SQL Server: Performance, Recursion, and Pitfalls