---
title: "SQL Server CTE (Common Table Expression)"  
description: "(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, si"  
author: "Anubhav Sharma"  
published: 2026-04-26  
updated: 2026-04-27  
canonical: https://answers.mindstick.com/blog/237/sql-server-cte-common-table-expression  
category: "database"  
tags: ["database", "sql server"]  
reading_time: 3 minutes  

---

# SQL Server CTE (Common Table Expression)

A **Common Table [Expression](https://www.mindstick.com/blog/289/introduction-of-lambda-expression) (CTE)** in [Microsoft](https://www.mindstick.com/articles/12364/microsoft-powerpoint-gets-real-time-speech-to-text-translation) [SQL Server](https://www.mindstick.com/articles/34/create-table-in-microsoft-sql-server) is a [temporary](https://www.mindstick.com/forum/2292/how-to-set-temporary-path-of-jdk-in-windows) result set that you can [reference](https://www.mindstick.com/forum/774/reference-what-does-this-error-mean-in-php) 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:

```plaintext
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](https://www.mindstick.com/forum/161557/how-do-common-table-expressions-ctes-help-in-writing-recursive-queries-in-sql-server)** (hierarchies)
- Works like a **temporary view (but scoped to one query)**

## Simple Example

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

## CTE vs Subquery

| Feature | CTE | Subquery |
| --- | --- | --- |
| Readability | High | Medium |
| Reusability | Yes (within query) | No |
| Recursion | Supported | Not supported |

## Recursive CTE (Most Important)

Used for hierarchical data like:

- Employee → Manager
- Categories → Subcategories

### Example: Employee Hierarchy

```plaintext
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](https://www.mindstick.com/forum/161549/how-do-you-write-a-recursive-query-to-fetch-all-descendants-in-a-self-referencing-table)** → joins back to CTE
- Repeats until no more rows

## CTE with Multiple Usage

You can use multiple CTEs:

```plaintext
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

```plaintext
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](https://www.mindstick.com/interview/94/why-can-a-group-by-or-order-by-clause-be-expensive-to-process) → 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](https://www.mindstick.com/forum/160936/how-can-sql-profiler-help-in-performance-tuning) requires indexed temp tables
- When [recursion depth](https://www.mindstick.com/forum/159426/python-max-recursion-depth-exceeded-how-prevent-and-fix-it) 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.

---

Original Source: https://answers.mindstick.com/blog/237/sql-server-cte-common-table-expression

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.