Using CTEs (Common Table Expressions) in SQL Server

If you need to break a complex SQL query into smaller steps, a CTE (Common Table Expression) is a simple way to do it. SQL Server has good support for them, you just need to learn the syntax.

A CTE can be thought of as being a temporary result set that you define. To do so, start your query using the WITH keyword. The result set only exists for the duration of that query.

Let's take a basic example:

WITH ActiveCustomers AS
(
    SELECT CustomerId, Name
    FROM dbo.Customers
    WHERE IsActive = 1
)
SELECT *
FROM ActiveCustomers;

This works like a named subquery. It can make queries easier to read compared to nested SELECT statements.

You can also use multiple CTEs in the same query, just separate them with a comma:

WITH ActiveCustomers AS
(
    SELECT CustomerId, Name
    FROM dbo.Customers
    WHERE IsActive = 1
),
RecentOrders AS
(
    SELECT OrderId, CustomerId
    FROM dbo.Orders
    WHERE CreatedUtc > DATEADD(day, -30, GETUTCDATE())
)
SELECT c.Name, o.OrderId
FROM ActiveCustomers c
JOIN RecentOrders o ON c.CustomerId = o.CustomerId;

Another common use case is recursive queries, for example when working with hierarchical data.

WITH CategoryTree AS
(
    SELECT CategoryId, ParentCategoryId, Name
    FROM dbo.Categories
    WHERE ParentCategoryId IS NULL

    UNION ALL

    SELECT c.CategoryId, c.ParentCategoryId, c.Name
    FROM dbo.Categories c
    INNER JOIN CategoryTree t ON c.ParentCategoryId = t.CategoryId
)
SELECT *
FROM CategoryTree;

This example walks a parent-child hierarchy starting from the root.

A few practical notes:

• A CTE only exists for the duration of the statement. It is not stored permanently anywhere.
• CTEs can improve readability, especially for complex queries.
• A CTE does not automatically improve performance compared to subqueries.
• Recursive CTEs are useful for hierarchical data like trees and organizational structures.

The rule of thumb is simple: use CTEs to make complex queries easier to read and structure, especially when you would otherwise use nested subqueries.

Happier querying!