SQL Server CTEs, Recursive Queries and Derived Tables (2026)

June 2026  |  15 min read  |  SQL Server, T-SQL, CTE, Recursive  |  — views

Common Table Expressions unlock hierarchical queries, cleaner SQL, and powerful set-based patterns in T-SQL

Common Table Expressions — CTEs — are one of the most valuable features in T-SQL. They let you name a result set once and reference it clearly, turning deeply nested subqueries into readable, self-documenting SQL. More importantly, recursive CTEs solve the classic "walk a tree" problem — employee hierarchies, bill-of-materials explosions, date sequences — that would otherwise require cursors or application-side code. This guide covers everything from basic WITH syntax through advanced recursive patterns and performance considerations, with five or more complete T-SQL examples you can run immediately.

---

 1. What Is a CTE? The WITH Syntax

A Common Table Expression is a named temporary result set you define at the start of a query using the WITH keyword. It exists only for the duration of that single statement — it is not persisted, not stored in tempdb, and not visible to other queries or batches.

-- Basic CTE syntax
WITH CTE_Name (Column1, Column2, ...)
AS (
    -- Any valid SELECT statement
    SELECT Column1, Column2, ...
    FROM   SomeTable
    WHERE  SomeCondition
)
SELECT *
FROM   CTE_Name
WHERE  Column1 = 'something';

The column list after the CTE name is optional — SQL Server infers column names from the SELECT unless you need to rename them or the CTE is recursive (where anchor and recursive member column names must match).

When to choose a CTE over alternatives

• Over a subquery — when the same derived set is referenced more than once, or when deep nesting makes the query unreadable.
• Over a view — when the logic is query-specific and does not need to be reused across other queries or stored procedures.
• Over a temp table — when you do not need to index the intermediate result or access it in a different scope.
• Required for recursion — recursive queries are only possible with a CTE; there is no recursive subquery syntax in T-SQL.

 2. Non-Recursive CTE: Basic Syntax and Chaining

The simplest use of a CTE is as a named subquery that improves readability. The example below identifies all orders placed in the last 30 days and calculates the total per customer — but instead of nesting a subquery, the filtering logic lives in a named CTE.

-- Non-recursive CTE: recent orders per customer
WITH RecentOrders AS (
    SELECT
        CustomerID,
        OrderID,
        OrderDate,
        TotalAmount
    FROM   Sales.Orders
    WHERE  OrderDate >= DATEADD(DAY, -30, GETDATE())
)
SELECT
    c.CustomerName,
    COUNT(r.OrderID)    AS OrderCount,
    SUM(r.TotalAmount)  AS Revenue30Days
FROM   RecentOrders  r
JOIN   Sales.Customers c ON c.CustomerID = r.CustomerID
GROUP BY c.CustomerName
ORDER BY Revenue30Days DESC;

You can define multiple CTEs in one WITH clause by separating them with commas. Later CTEs can reference earlier ones — this is called chaining.

-- Multiple CTEs in one WITH clause (chaining)
WITH
ActiveCustomers AS (
    SELECT CustomerID, CustomerName, Region
    FROM   Sales.Customers
    WHERE  IsActive = 1
),
RecentOrders AS (
    SELECT CustomerID, COUNT(*) AS OrderCount
    FROM   Sales.Orders
    WHERE  OrderDate >= DATEADD(DAY, -90, GETDATE())
    GROUP  BY CustomerID
),
CustomerSummary AS (
    -- This CTE references both previous CTEs
    SELECT
        a.CustomerName,
        a.Region,
        COALESCE(r.OrderCount, 0) AS Orders90Days
    FROM  ActiveCustomers a
    LEFT  JOIN RecentOrders r ON r.CustomerID = a.CustomerID
)
SELECT *
FROM   CustomerSummary
WHERE  Orders90Days > 0
ORDER  BY Orders90Days DESC;

 3. CTE vs Derived Table vs Temp Table vs View

Choosing the right construct depends on reuse, scope, and performance requirements.

Performance note: SQL Server does not materialise a CTE by default — it inlines the CTE definition at every reference point in the query plan. If your CTE is expensive and referenced twice, SQL Server may execute it twice. Use a #temp table when you need guaranteed single-evaluation materialisation or when you need to add an index to the intermediate result.

 4. Multiple CTEs: Referencing One CTE from Another

In this complete example three CTEs work together: one calculates product sales totals, a second joins them to categories, and a third ranks the results. Each CTE is defined once and referenced by name — no nesting required.

-- Three chained CTEs: ranked product sales by category
WITH

-- Step 1: aggregate order line items into product-level sales
ProductSales AS (
    SELECT
        ol.ProductID,
        SUM(ol.Quantity * ol.UnitPrice) AS TotalRevenue,
        SUM(ol.Quantity)                AS UnitsSold
    FROM   Sales.OrderLines ol
    JOIN   Sales.Orders     o  ON o.OrderID = ol.OrderID
    WHERE  YEAR(o.OrderDate) = 2025
    GROUP  BY ol.ProductID
),

-- Step 2: add category info from the product catalogue
ProductWithCategory AS (
    SELECT
        p.ProductName,
        p.CategoryID,
        c.CategoryName,
        ps.TotalRevenue,
        ps.UnitsSold
    FROM   ProductSales        ps
    JOIN   Catalogue.Products  p  ON p.ProductID  = ps.ProductID
    JOIN   Catalogue.Categories c ON c.CategoryID = p.CategoryID
),

-- Step 3: rank products within each category
RankedProducts AS (
    SELECT
        ProductName,
        CategoryName,
        TotalRevenue,
        UnitsSold,
        RANK() OVER (PARTITION BY CategoryID ORDER BY TotalRevenue DESC) AS RankInCategory
    FROM  ProductWithCategory
)

-- Final query: top 3 products per category
SELECT CategoryName, RankInCategory, ProductName, TotalRevenue, UnitsSold
FROM   RankedProducts
WHERE  RankInCategory <= 3
ORDER  BY CategoryName, RankInCategory;

 5. Recursive CTE Structure: Anchor + Recursive Member

A recursive CTE has two mandatory parts joined by UNION ALL:

1. Anchor member — a regular SELECT that produces the starting set (the "seed" rows). It runs once.
2. Recursive member — a SELECT that references the CTE itself and joins to the base table. SQL Server runs this repeatedly, feeding each iteration's output as the next iteration's input, until no more rows are returned.

-- Recursive CTE template
WITH RecursiveCTE (Col1, Col2, ..., Level)
AS (
    -- ANCHOR: starting rows (runs once)
    SELECT Col1, Col2, ..., 0 AS Level
    FROM   BaseTable
    WHERE  ParentColumn IS NULL   -- or some root condition

    UNION ALL

    -- RECURSIVE MEMBER: join CTE result to next level
    SELECT b.Col1, b.Col2, ..., r.Level + 1
    FROM   BaseTable         b
    JOIN   RecursiveCTE      r  ON r.Col1 = b.ParentColumn
)
SELECT *
FROM   RecursiveCTE
OPTION (MAXRECURSION 100);   -- safety limit; default is 100

 6. Recursive CTE Use Case 1: Employee–Manager Hierarchy

The classic recursive CTE problem: a self-referencing Employees table where each row has a ManagerID pointing to another row in the same table. Recursion lets you traverse all levels of the org chart in a single query.

-- Sample data setup
CREATE TABLE #Employees (
    EmployeeID   INT PRIMARY KEY,
    EmployeeName NVARCHAR(100),
    JobTitle     NVARCHAR(100),
    ManagerID    INT NULL   -- NULL = top of the org chart
);

INSERT INTO #Employees VALUES
(1,  'Sarah Chen',    'CEO',                NULL),
(2,  'Marcus Webb',   'VP Engineering',     1),
(3,  'Priya Sharma',  'VP Sales',           1),
(4,  'Tom Nguyen',    'Engineering Manager',2),
(5,  'Lisa Patel',    'Senior Engineer',    4),
(6,  'David Kim',     'Engineer',           4),
(7,  'Ana Ferreira',  'Sales Manager',      3),
(8,  'James Obi',     'Account Executive',  7),
(9,  'Yuki Tanaka',   'Account Executive',  7);

-- Recursive CTE: full org chart with depth and path
WITH OrgChart (EmployeeID, EmployeeName, JobTitle, ManagerID, Level, OrgPath)
AS (
    -- Anchor: CEO (no manager)
    SELECT
        EmployeeID,
        EmployeeName,
        JobTitle,
        ManagerID,
        0                          AS Level,
        CAST(EmployeeName AS NVARCHAR(500)) AS OrgPath
    FROM #Employees
    WHERE ManagerID IS NULL

    UNION ALL

    -- Recursive: all direct reports of the current level
    SELECT
        e.EmployeeID,
        e.EmployeeName,
        e.JobTitle,
        e.ManagerID,
        oc.Level + 1,
        CAST(oc.OrgPath + N' > ' + e.EmployeeName AS NVARCHAR(500))
    FROM #Employees   e
    JOIN OrgChart     oc ON oc.EmployeeID = e.ManagerID
)
SELECT
    REPLICATE('    ', Level) + EmployeeName AS IndentedName,
    JobTitle,
    Level,
    OrgPath
FROM  OrgChart
ORDER BY OrgPath;

DROP TABLE #Employees;

The OrgPath column builds the full reporting chain as a string. REPLICATE adds visual indentation. The output looks like a printed org chart.

 7. Recursive CTE Use Case 2: Bill of Materials (Parts Explosion)

A bill-of-materials (BOM) table records parent/child relationships between parts. A "parts explosion" query expands a finished product into all of its sub-components at every depth level — ideal for manufacturing, software dependency graphs, or any tree structure.

CREATE TABLE #BOM (
    ComponentID   INT,
    ComponentName NVARCHAR(100),
    ParentID      INT NULL,  -- NULL = top-level product
    Quantity      DECIMAL(10,2)
);

INSERT INTO #BOM VALUES
(1,  'Bicycle',         NULL, 1),
(2,  'Frame Assembly',  1,    1),
(3,  'Wheel Assembly',  1,    2),  -- 2 wheels
(4,  'Frame',           2,    1),
(5,  'Fork',            2,    1),
(6,  'Rim',             3,    1),
(7,  'Tyre',            3,    1),
(8,  'Spokes',          3,    36),
(9,  'Hub',             3,    1);

WITH BOMExplosion (ComponentID, ComponentName, ParentID, Depth, CumulativeQty, BOMPath)
AS (
    -- Anchor: top-level product
    SELECT
        ComponentID,
        ComponentName,
        ParentID,
        0                               AS Depth,
        Quantity                        AS CumulativeQty,
        CAST(ComponentName AS NVARCHAR(500)) AS BOMPath
    FROM #BOM
    WHERE ParentID IS NULL

    UNION ALL

    -- Recursive: child components
    SELECT
        c.ComponentID,
        c.ComponentName,
        c.ParentID,
        b.Depth + 1,
        b.CumulativeQty * c.Quantity,   -- multiply quantities down the tree
        CAST(b.BOMPath + N' > ' + c.ComponentName AS NVARCHAR(500))
    FROM #BOM         c
    JOIN BOMExplosion b ON b.ComponentID = c.ParentID
)
SELECT
    Depth,
    REPLICATE('  ', Depth) + ComponentName AS Component,
    CumulativeQty                          AS TotalQtyNeeded,
    BOMPath
FROM  BOMExplosion
ORDER BY BOMPath;

DROP TABLE #BOM;

The key line is b.CumulativeQty * c.Quantity — multiplying the parent's accumulated quantity by the child's unit quantity gives the total count of each leaf-level part needed to build one top-level product.

 8. Recursive CTE Use Case 3: Date Sequence Generation

SQL Server has no built-in function to generate a list of dates between two values. A recursive CTE fills that gap neatly — and the result can be used in reporting queries to fill gaps for days with no transactions.

-- Generate every date between @Start and @End (inclusive)
DECLARE @Start DATE = '2025-01-01';
DECLARE @End   DATE = '2025-01-31';

WITH DateSeries (DateValue)
AS (
    -- Anchor: first date
    SELECT @Start

    UNION ALL

    -- Recursive: add one day until we reach the end
    SELECT DATEADD(DAY, 1, DateValue)
    FROM   DateSeries
    WHERE  DateValue < @End
)
SELECT
    DateValue,
    DATENAME(WEEKDAY, DateValue) AS DayName,
    DATEPART(WEEK,    DateValue) AS WeekNumber
FROM  DateSeries
OPTION (MAXRECURSION 0);   -- needed for ranges > 100 days

For date ranges longer than 100 days you must specify OPTION (MAXRECURSION 0) (unlimited) or a value large enough to cover the range. For a full year that is 365 iterations — well within practical limits.

 9. Recursive CTE Use Case 4: String Splitting Without STRING_SPLIT

On SQL Server 2016+ you can use the built-in STRING_SPLIT, but the recursive CTE approach works on older versions and preserves ordinal position — which STRING_SPLIT does not guarantee until SQL Server 2022 (with the enable_ordinal argument).

-- Split a comma-delimited string into rows, preserving position
DECLARE @CSV NVARCHAR(500) = 'Alpha,Beta,Gamma,Delta,Epsilon';

WITH Splitter (Pos, Token, Remainder)
AS (
    -- Anchor: process first token
    SELECT
        1                                                         AS Pos,
        LEFT(@CSV, CHARINDEX(',', @CSV + ',') - 1)               AS Token,
        STUFF(@CSV, 1, CHARINDEX(',', @CSV + ','), '')            AS Remainder

    UNION ALL

    -- Recursive: process each subsequent token
    SELECT
        Pos + 1,
        LEFT(Remainder, CHARINDEX(',', Remainder + ',') - 1),
        STUFF(Remainder, 1, CHARINDEX(',', Remainder + ','), '')
    FROM  Splitter
    WHERE Remainder <> ''
)
SELECT Pos, Token
FROM   Splitter
ORDER  BY Pos;

Each iteration strips the leftmost token (everything before the first comma) into Token and puts the rest in Remainder. When Remainder becomes empty, recursion stops. The result is a clean row-per-element table with ordinal position.

 10. MAXRECURSION: Default, Setting, and When to Use

SQL Server enforces a recursion depth limit to prevent accidental infinite loops. The behaviour is controlled by the MAXRECURSION query hint:

 11. CTE Performance: Materialised or Inlined?

This is one of the most common misconceptions about CTEs. SQL Server does not automatically materialise a CTE. It treats a CTE as a named macro — the optimizer substitutes the CTE definition at every reference point before building the execution plan.

Implications

• CTE referenced twice = executed twice. If your CTE aggregates 10 million rows and you join it to itself, the aggregation runs twice. Check the execution plan (Ctrl+M in SSMS) to confirm.
• Statistics and cardinality estimates flow through a CTE just like a subquery, so the optimizer can still push predicates and choose good join strategies.
• Recursive CTEs are partly materialised internally by SQL Server — each iteration's output is spooled in tempdb to feed the next iteration. This is unavoidable.

When to switch to a temp table

• The CTE result is referenced more than twice in the outer query.
• The CTE performs heavy aggregation or many joins and the execution plan shows repeated expensive sub-trees.
• You need to add an index to the intermediate result to speed up subsequent joins.

-- If this CTE is referenced 3 times, consider a temp table instead
WITH ExpensiveCTE AS (
    SELECT CustomerID, SUM(Amount) AS Total
    FROM   dbo.Transactions
    GROUP  BY CustomerID
)
-- If you see ExpensiveCTE appear 3 times below, materialise it
SELECT a.CustomerID, a.Total, b.Total AS PrevTotal
FROM   ExpensiveCTE a
JOIN   ExpensiveCTE b ON b.CustomerID = a.CustomerID   -- double execution!
WHERE  a.Total > b.Total * 1.1;

 12. Modifying Data with CTEs: UPDATE and DELETE

You can issue UPDATE and DELETE statements directly against a CTE. SQL Server applies the change to the underlying base table. This is the cleanest way to deduplicate rows — identify duplicates using ROW_NUMBER() in a CTE, then delete those where the row number is greater than 1.

-- Create a table with duplicate rows
CREATE TABLE #Orders (
    OrderID    INT IDENTITY(1,1) PRIMARY KEY,
    CustomerID INT,
    OrderDate  DATE,
    Amount     DECIMAL(10,2)
);

INSERT INTO #Orders (CustomerID, OrderDate, Amount) VALUES
(101, '2025-03-01', 250.00),
(101, '2025-03-01', 250.00),  -- exact duplicate
(102, '2025-03-02', 175.00),
(102, '2025-03-02', 175.00),  -- exact duplicate
(103, '2025-03-03', 320.00);

-- Deduplication: keep only the first occurrence per (CustomerID, OrderDate, Amount)
WITH DuplicatesCTE AS (
    SELECT
        OrderID,
        ROW_NUMBER() OVER (
            PARTITION BY CustomerID, OrderDate, Amount
            ORDER BY     OrderID          -- keep lowest OrderID
        ) AS RowNum
    FROM #Orders
)
DELETE FROM DuplicatesCTE
WHERE  RowNum > 1;

-- Verify
SELECT * FROM #Orders;

DROP TABLE #Orders;

The same pattern works for UPDATE. Put your filter logic in the CTE and then UPDATE CTE_Name SET Column = value WHERE condition.

-- CTE-based UPDATE: apply a 10% discount to top 5 customers by revenue
WITH TopCustomers AS (
    SELECT TOP 5
        CustomerID,
        DiscountRate
    FROM   Sales.Customers
    ORDER  BY LifetimeRevenue DESC
)
UPDATE TopCustomers
SET    DiscountRate = DiscountRate + 0.10;

 13. Common CTE Pitfalls

1. CTE is not reusable across batches or stored procedures

A CTE exists only for the single SELECT/INSERT/UPDATE/DELETE that immediately follows the WITH block. You cannot define a CTE in one batch and reference it in another batch, and you cannot call a CTE from inside a stored procedure unless the WITH and the consuming statement are part of the same stored procedure body.

2. You cannot ORDER BY inside a CTE

SQL Server does not allow an ORDER BY clause inside a CTE definition (unless paired with TOP or FETCH). Result sets inside CTEs are inherently unordered. Apply ORDER BY in the final outer query.

-- WRONG: ORDER BY inside CTE body (without TOP) will error
WITH BadCTE AS (
    SELECT ProductID, ProductName
    FROM   Catalogue.Products
    ORDER  BY ProductName   -- ERROR: not allowed here
)
SELECT * FROM BadCTE;

-- CORRECT: ORDER BY belongs on the outer query
WITH GoodCTE AS (
    SELECT ProductID, ProductName
    FROM   Catalogue.Products
)
SELECT * FROM GoodCTE ORDER BY ProductName;

3. Ensure recursive termination — do not rely on MAXRECURSION alone

MAXRECURSION is a safety guard, not a loop exit. If your recursive member's WHERE clause never evaluates to false, SQL Server will keep running until it hits the limit and throws an error. Always verify your recursive member logically converges — the join condition should walk the hierarchy toward leaf nodes, not cycle back to ancestors.

4. Column name ambiguity in recursive CTEs

The anchor and recursive member must produce the same number of columns with compatible data types. If a column name differs between the two members, define explicit column aliases in the WITH CTE_Name (col1, col2, ...) header.

5. Avoid DISTINCT inside a CTE when it interacts with recursion

Using DISTINCT, GROUP BY, HAVING, TOP, or outer joins in the recursive member of a CTE is not supported in SQL Server and will cause a compile error. These operators can only appear in the anchor member or the final outer query.

 Quick Reference Card

Read Next

Frequently Asked Questions