---
title: "What are Common Table Expressions (CTEs) in SQL Server?"  
description: "What are Common Table Expressions (CTEs) in SQL Server?"  
author: "Revati S Misra"  
published: 2023-10-18  
updated: 2023-10-19  
canonical: https://www.mindstick.com/forum/160189/what-are-common-table-expressions-ctes-in-sql-server  
category: "mssql server"  
tags: ["sql server", "sql", "database table"]  
reading_time: 3 minutes  

---

# What are Common Table Expressions (CTEs) in SQL Server?

What are [Common](https://www.mindstick.com/articles/23170/10-most-common-accounting-mistakes-of-small-business) [Table](https://www.mindstick.com/articles/43918/how-to-design-table-using-bootstrap) [Expressions](https://www.mindstick.com/forum/33876/what-is-the-syntax-for-lambda-expressions-in-vb-dot-net) (CTEs) in [SQL Server](https://www.mindstick.com/articles/12999/what-is-table-valued-function-in-sql-server)?

## Replies

### Reply by Aryan Kumar

**Common Table Expressions (CTEs)** in [SQL](https://www.mindstick.com/articles/13115/types-of-keys-in-sql-or-oracle-database) [Server](https://www.mindstick.com/articles/43769/what-is-serverless-architecture-is-it-worth-switching-over) are temporary result sets that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs are defined using the **WITH** clause and provide a way to break down complex queries into more manageable, modular, and self-contained components. They improve query readability and can be particularly useful in recursive queries. Here's how you define and use CTEs in SQL Server:

## Syntax for Defining a CTE:

```plaintext
WITH CTE_Name (Column1, Column2, ...) AS (
    -- CTE Query
    SELECT ...
    FROM ...
    WHERE ...
)
```

- **CTE_Name**: The name you give to the CTE. This name is used to reference the CTE within the main query.
- **(Column1, Column2, ...)**: Optional. Specifies the column names for the CTE, making it easy to alias or manipulate the column names.
- **-- CTE Query**: The SQL query that defines the CTE and produces the temporary result set.

## Using a CTE in a Query:

Once a CTE is defined, you can reference it within a query, as shown below:

```plaintext
SELECT ...
FROM CTE_Name
WHERE ...
```

## Examples of Common Use Cases for CTEs in SQL Server:

**Recursive Queries**: CTEs are often used for recursive queries. For example, to represent hierarchical data like an organizational chart, you can use a CTE to traverse the tree structure.

**Data Transformation**: CTEs can be employed to transform or restructure data. You might pivot data, unpivot data, or generate specific calculations using CTEs.

**Query Simplification**: CTEs can simplify complex queries by breaking them down into smaller, more understandable parts. Each CTE can focus on a specific subset of the data or a particular task.

**Self-Containment**: CTEs are self-contained, meaning they can reference themselves or other CTEs defined within the same query. This avoids the need to reference the same subquery logic multiple times.

**Reusable Logic**: CTEs promote code reuse within a query and across multiple queries. You can define a CTE once and reference it multiple times within the same query or in different queries.

Here's a simple example of a CTE in SQL Server that calculates the factorial of a number using a recursive CTE:

```plaintext
WITH FactorialCTE (Number, Factorial) AS (
    SELECT 0, 1
    UNION ALL
    SELECT Number + 1, Factorial * (Number + 1)
    FROM FactorialCTE
    WHERE Number < 9
)
SELECT Factorial
FROM FactorialCTE
WHERE Number = 9;
```

In this example, the CTE **FactorialCTE** recursively calculates the factorial of a number, starting from 0 and progressing to 9. The main query then selects the factorial for the number 9.


---

Original Source: https://www.mindstick.com/forum/160189/what-are-common-table-expressions-ctes-in-sql-server

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