How to Use Common Table Expressions (CTEs) in SQL Server for Readable Queries

Common Table Expressions (CTEs) are a powerful SQL Server feature that can dramatically improve query readability and maintainability.

Introduced in SQL Server 2005, CTEs let you define a temporary result set that you can reference within a SELECT, INSERT, UPDATE, DELETE, or MERGE statement.

Basic CTE Syntax

A CTE follows this pattern:

WITH CTE_Name AS (
    -- Your query here
)
SELECT * FROM CTE_Name;

The main components are:

• The WITH keyword to start the CTE
• A name for your CTE
• The AS keyword
• Parentheses containing your query
• A statement that references the CTE

Why Use CTEs?

CTEs offer several advantages:

• Improved readability: Breaking complex queries into named, logical segments
• Self-referencing capability: Useful for hierarchical or recursive data
• Query simplification: Reducing nested subqueries
• Code reusability: Using the same temporary result multiple times in a query

Simple CTE Example

Here's a basic example that calculates average order values by customer category:

-- Without CTE
SELECT 
    c.CustomerCategory,
    SUM(o.TotalAmount) / COUNT(DISTINCT o.OrderID) AS AvgOrderValue
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerCategory;

-- With CTE
WITH OrderSummary AS (
    SELECT 
        c.CustomerCategory,
        o.OrderID,
        o.TotalAmount
    FROM Customers c
    JOIN Orders o ON c.CustomerID = o.CustomerID
)
SELECT 
    CustomerCategory,
    SUM(TotalAmount) / COUNT(DISTINCT OrderID) AS AvgOrderValue
FROM OrderSummary
GROUP BY CustomerCategory;

The CTE version clearly separates the data gathering from the aggregation logic.

Multiple CTEs in a Single Query

You can chain CTEs for even more complex scenarios:

WITH 
CustomerOrders AS (
    SELECT 
        c.CustomerID,
        c.CustomerName,
        COUNT(o.OrderID) AS OrderCount
    FROM Customers c
    LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
    GROUP BY c.CustomerID, c.CustomerName
),
OrderCategories AS (
    SELECT
        CustomerID,
        CASE 
            WHEN OrderCount = 0 THEN 'Inactive'
            WHEN OrderCount BETWEEN 1 AND 5 THEN 'Regular'
            ELSE 'VIP'
        END AS CustomerCategory
    FROM CustomerOrders
)
SELECT 
    c.CustomerName,
    o.CustomerCategory
FROM CustomerOrders c
JOIN OrderCategories o ON c.CustomerID = o.CustomerID
ORDER BY o.CustomerCategory, c.CustomerName;

Recursive CTEs

One of the most powerful CTE features is recursion, which is perfect for hierarchical data like organizational charts or category trees:

WITH EmployeeHierarchy AS (
    -- Anchor member (starting point)
    SELECT 
        EmployeeID,
        EmployeeName,
        ManagerID,
        0 AS Level
    FROM Employees
    WHERE ManagerID IS NULL -- Start with top-level employees
    
    UNION ALL
    
    -- Recursive member (references itself)
    SELECT 
        e.EmployeeID,
        e.EmployeeName,
        e.ManagerID,
        eh.Level + 1
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT 
    EmployeeID,
    EmployeeName,
    Level,
    REPLICATE('--', Level) + EmployeeName AS HierarchyDisplay
FROM EmployeeHierarchy
ORDER BY Level, EmployeeName;

This query produces an indented organization chart starting from top-level managers.

CTEs vs. Temporary Tables or Table Variables

Unlike temporary tables or table variables, CTEs:

• Exist only during query execution
• Don't require explicit cleanup
• Can't have indexes added to them
• Are primarily for improving query structure and readability

Best Practices

1. Use meaningful names that describe what the data represents
2. Keep individual CTEs focused on a single logical operation
3. Comment complex CTEs to explain their purpose
4. Consider performance - CTEs are not always more efficient than subqueries
5. Avoid excessive nesting - if your query becomes too complex, consider stored procedures or multiple queries

When Not to Use CTEs

CTEs might not be the best choice when:

• You need to reference the same large dataset multiple times (temp tables may be more efficient)
• You need to add indexes for performance optimization
• Your recursive CTE might exceed the default recursion limit (100)

By mastering CTEs, you can write SQL that's not only more maintainable but also easier to understand and debug.