SQL Server CTE (Common Table Expression)

A Common Table Expression (CTE) in Microsoft SQL Server is a temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. It improves readability, simplifies complex queries, and is especially useful for hierarchical and recursive data.

What is a CTE?

A CTE is defined using the WITH keyword and exists only for the duration of a single query.

Basic syntax:

WITH CTE_Name (Column1, Column2, ...)
AS
(
    SELECT Column1, Column2
    FROM TableName
    WHERE Condition
)
SELECT *
FROM CTE_Name;

Why use CTE?

• Makes complex queries clean and readable
• Avoids repeating subqueries
• Helps in recursive queries (hierarchies)
• Works like a temporary view (but scoped to one query)

Simple Example

WITH ActiveUsers AS
(
    SELECT Id, Name
    FROM Users
    WHERE IsActive = 1
)
SELECT *
FROM ActiveUsers;

CTE vs Subquery

Recursive CTE (Most Important)

Used for hierarchical data like:

• Employee → Manager
• Categories → Subcategories

Example: Employee Hierarchy

WITH EmployeeCTE AS
(
    -- Anchor query
    SELECT Id, Name, ManagerId
    FROM Employees
    WHERE ManagerId IS NULL

    UNION ALL

    -- Recursive query
    SELECT e.Id, e.Name, e.ManagerId
    FROM Employees e
    INNER JOIN EmployeeCTE c
        ON e.ManagerId = c.Id
)
SELECT *
FROM EmployeeCTE;

How it works:

• Anchor query → gets top-level records
• Recursive query → joins back to CTE
• Repeats until no more rows

CTE with Multiple Usage

You can use multiple CTEs:

WITH DeptCount AS
(
    SELECT DepartmentId, COUNT(*) AS Total
    FROM Employees
    GROUP BY DepartmentId
),
HighCount AS
(
    SELECT *
    FROM DeptCount
    WHERE Total > 10
)
SELECT *
FROM HighCount;

CTE in INSERT / UPDATE / DELETE

UPDATE Example

WITH CTE AS
(
    SELECT Id, Salary
    FROM Employees
    WHERE DepartmentId = 1
)
UPDATE CTE
SET Salary = Salary + 1000;

Performance Considerations

• CTEs are not stored in memory permanently
• They are expanded like inline queries
• For large datasets, performance is similar to subqueries
• Recursive CTEs can be expensive → use OPTION (MAXRECURSION n)

Best Practices

• Keep CTEs small and focused
• Avoid deep recursion without limits
• Use meaningful names (e.g., EmployeeHierarchy)
• Prefer CTE over nested subqueries for readability

When NOT to Use CTE

• When query is very simple
• When performance tuning requires indexed temp tables
• When recursion depth is too large

Conclusion

CTEs in Microsoft SQL Server are a powerful way to write clean, maintainable, and hierarchical queries. They shine in recursive scenarios and complex query structuring, making them a must-know feature for SQL developers.