3 Ways to Write Cleaner SQL With Common Table Expressions

A Clean Solution for the Messiest Queries.

---

---

What is the CTE?

A CTE is a named query you can reference like a view within a single statement in SQL.

CTEs are not materialized unlike temporary tables. It does not hold any data if you look at the query plan.

There are three main CTE types:

1. Standard, non-recursive CTEs compute their results from tables, views, or other CTEs, then pass that result to the outer statement.

2. Recursive CTEs. They reference themselves in their definition.

3. Nested CTEs. They define the expression within the definition of another.

---

CTE Syntax:

CTE requires a name and a query definition.

Example:

WITH SalesSummary AS (
    SELECT 
        SalesPersonID, 
        SUM(TotalDue) AS TotalSales
    FROM 
        Sales.SalesOrderHeader
    GROUP BY 
        SalesPersonID
)
SELECT 
    SalesPersonID, 
    TotalSales
FROM 
    SalesSummary

A single WITH clause can contain multiple sequential CTE definitions. Separate the definitions with commas, and define each CTE before any CTE that references it.

You can define multiple common table expressions under a single WITH clause. Separate them with commas.

Each subsequent expression can reference any previously defined expression in the same clause.

Example:

WITH RawData AS (
    SELECT 
        ProductID, 
        OrderQty, 
        UnitPrice
    FROM 
        Sales.SalesOrderDetail
),
AggregatedData AS (
    SELECT 
        ProductID, 
        SUM(OrderQty) AS TotalQty, 
        AVG(UnitPrice) AS AveragePrice
    FROM 
        RawData
    GROUP BY 
        ProductID
)
SELECT 
    ProductID, 
    TotalQty, 
    AveragePrice
FROM 
    AggregatedData

The AggregatedData expression references the RawData expression in order to run its calculations.

---

Nested Expressions:

In SQL Server 2025 and Microsoft Fabric you can nest CTEs: when one expression is defined within the definition of another.

Example:

WITH OuterExpression AS (
    WITH InnerExpression AS (
        SELECT 
          Column1, 
          Column2
        FROM 
          SourceTable
    )
    SELECT 
      * 
    FROM 
      InnerExpression
)
SELECT
  * 
FROM 
  OuterExpression

Keep the nesting depth to 64 levels max.

---

Rules and Limitations:

• ORDER BY is not allowed inside a CTE unless you combine it with TOP or OFFSET/FETCH. Do the final sorting in the outermost query.

• The CTE definition cannot contain INTO, FOR BROWSE, or the OPTION clauses.

• The outer statement must reference the CTE. An unreferenced CTE will result in a syntax error.

• Each CTE can reference previously defined CTEs in the same WITH clause, but you cannot use forward references.

---

Recursive CTEs:

A recursive CTE references itself. You can run queries for hierarchical or graph-structured data, for example, organization charts.

A recursive expression consists of two parts:

1. The anchor member. It executes first and creates the initial result set.

2. The recursive member. It references the expression itself to join with the base table.

The UNION ALL operator connects the anchor and recursive members.

Example:

WITH EmployeeHierarchy AS (
    -- Anchor member
    SELECT 
        EmployeeID, 
        ManagerID, 
        EmployeeName, 
        0 AS Depth
    FROM 
        Employees
    WHERE 
        ManagerID IS NULL
    
    UNION ALL
    
    -- Recursive member
    SELECT 
        employee.EmployeeID, 
        employee.ManagerID, 
        employee.EmployeeName, 
        hierarchy.Depth + 1
    FROM 
        Employees AS employee
    INNER JOIN 
        EmployeeHierarchy AS hierarchy 
        ON employee.ManagerID = hierarchy.EmployeeID
)
SELECT 
  * 
FROM 
  EmployeeHierarchy

The EmployeeHierarchy expression here traverses the organizational chart starting from the top-level manager.

The recursion will continue until the recursive member returns no more rows or the query reaches the maximum recursion limit. 0 will remove the limit.

SQL Server sets the default limit to 100 levels, but you can modify this limit with the MAXRECURSION hint.

Set the hint explicitly in any recursive CTE where depth might exceed the default 100.

Always create a covering index for the parent-child relationship on the column you use in the recursive join.

Without it, SQL Server does a full scan of the base table on every iteration.

---

Supported Operations in CTEs:

Common table expressions support SELECT, INSERT, UPDATE, DELETE, or MERGE statements.

You can modify the underlying table data with the common table expression.

The expression must reference only one base table to modify the data.

Example:

WITH RecentOrders AS (
    SELECT 
      OrderStatus, 
      OrderDate
    FROM 
      Sales.SalesOrderHeader
    WHERE 
      OrderDate > ‘2025-01-01’
)
UPDATE 
  RecentOrders
SET 
  OrderStatus = 5
WHERE 
  OrderStatus = 1

The update statement modifies the SalesOrderHeader table for rows that meet the criteria you defined in the RecentOrders expression.

---

Use Cases and Advanced Applications:

1. You can use window functions within an expression to locate and remove duplicate rows.

Example:

WITH DuplicateRows AS (
    SELECT 
        RowID, 
        ROW_NUMBER() OVER (PARTITION BY Column1, Column2 ORDER BY RowID) 
          AS RowNum
    FROM TargetTable
)
DELETE FROM 
  DuplicateRows
WHERE 
  RowNum > 1

2. Hierarchical data processing. Use recursive CTEs to process tree-structured data.

3. Performance Enhancement: CTEs reduce nested subqueries and redundant calculations, so the query optimizer will generate more efficient execution plans.

4. Simplify Complex Aggregations: CTEs work well for multi-tiered aggregations where one aggregation feeds into another.

Example:

WITH monthly_sales AS (
    SELECT
        fact_sales.ProductKey,
        dim_date.EnglishMonthName,
        SUM(fact_sales.OrderQuantity) AS TotalOrdersByMonth
    FROM 
        FactInternetSales AS fact_sales
    INNER JOIN 
        DimDate AS dim_date 
        ON dim_date.DateKey = fact_sales.OrderDateKey
    GROUP BY 
        fact_sales.ProductKey, 
        dim_date.CalendarYear, 
        dim_date.MonthNumberOfYear,
        dim_date.EnglishMonthName
)
SELECT 
  ProductKey, 
  AVG(TotalOrdersByMonth) AS AverageMonthlyOrders
FROM 
  monthly_sales
GROUP BY 
  ProductKey
ORDER BY 
  ProductKey;

5. SQL Server 2025 and Azure introduced vector data types.

You can process vector embeddings within an expression to run similarity searches for large datasets.

Vector functions can calculate distances between vectors, and vector-search features can return approximate nearest-neighbor results.

---

Performance:

• Excessive CTE nesting can lead to performance bottlenecks, especially with large datasets or deep recursion.

• If you reference a CTE more than once in the outer query, SQL Server will re-execute the CTE’s inner query for each reference case.

• CTE does not hold any data if you look at the query plan.

• Recursive CTE performance depends on the index covering the recursive join. Every iteration will scan the base table without an index on the parent column.

• The SQL Server query optimizer treats common table expressions as macros. The engine does not materialize the result set into memory or disk by default. You cannot create indexes directly on it because the expression is not a physical table.

• If performance is slow or CTE’s inner query scans more than a few thousand rows and is referenced more than once, you should optimize the base tables or use a temporary table.

---

Subscribe to get more weekly deep dives.

What is the query that always slows you down?

Let me know in the comments. 👇

---