CTE in SQL Server: Learn Common Table Expressions with Examples

CTE in SQL simplifies complex queries to make code more readable and provides a structured way to work with temporary result sets. 

In this article, we will explore what CTE is, its syntax, examples, why we need it, its types, advantages, and disadvantages of Common Table Expression (CTE), and how to use it in SQL Server.

What is CTE in SQL Server?

A Common Table Expression (CTE) in SQL Server is a temporary result set that exists only within the scope of a single query and is automatically discarded once the query’s execution is completed.

A CTE can be reference within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs make complex SQL queries more readable and manageable by breaking them into smaller, named, and reusable pieces. 

They are defined using the WITH keyword, followed by a CTE name and a query defining the CTE’s result set.

CTE Syntax in SQL Server

The basic syntax of a CTE looks like this:

WITH CTE_Name (Column1, Column2, ...) AS (
    -- CTE Query
    SELECT ...
)
SELECT * FROM CTE_Name;  

• CTE_Name: This is the name of your CTE.
• (Column1, Column2, ...): This is an optional list of column names to define the CTE’s structure.
• AS: Indicates the start of the CTE query whose result set populates the CTE.
• SELECT ...: Your SQL query that defines the CTE.
• CTE_Name: At the last, we will use the CTE name in a query such as SELECT, INSERT, UPDATE, or DELETE statement.

CTE Example:

Let’s create a simple table and then use a CTE to retrieve data from it. Suppose we have an Employees table with columns EmployeeID, FirstName, LastName, and ManagerID. We want to find the employees who report directly to a specific manager. Here’s how we can do it with a CTE:

-- Creating a table.
CREATE TABLE Employees (
    EmployeeID INT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    ManagerID INT
);

-- Inserting records into the table.
INSERT INTO Employees VALUES
(1, 'Shekh', 'Ali', NULL),
(2, 'Rohit', 'Sharma', 1),
(3, 'Mike', 'Johnson', 1),
(4, 'Emily', 'Davis', 2),
(5, 'Richard', 'Brown', 2);

Using a CTE to retrieve employees reporting to ManagerID is 1.

WITH DirectReports AS (
    SELECT EmployeeID, FirstName, LastName
    FROM Employees
    WHERE ManagerID = 1
)
SELECT * FROM DirectReports;

Output:

In this example, the CTE DirectReports filters the Employees table to retrieve those reporting to ManagerID is 1.

Why do We Need CTE in SQL Server?

CTEs are useful in SQL Server for several reasons:

1. CTEs are valuable when we use the derived table (virtual table) multiple times within a single query. They allow us to define a temporary result set that can be referenced and reused throughout the query.
2. CTEs are beneficial when we need to create an alternative to a view in the database. While views are predefined queries stored in the database, CTEs provide an ad-hoc way to structure and simplify complex queries within a specific query context.
3. CTEs are helpful when we need to perform the same calculation multiple times on various query components simultaneously. They enable us to define the calculation logic once and apply it to different parts of a query, reducing redundancy and promoting consistency.
4. CTEs become indispensable when we need to utilize ranking functions such as ROW_NUMBER(), RANK(), and NTILE(). These functions often require partitioning and ordering data within a query, and CTEs can help organize and manage these operations efficiently.

Types of CTE in SQL Server

CTEs in SQL Server can be categorized into two main types: Recursive and Non-Recursive CTEs.

Recursive CTE

Recursive CTEs are used when you need to work with hierarchical data, where a record refers to other records within the same table. These CTEs typically consist of two parts: the anchor member and the recursive member.

• Anchor Member: The initial query that selects the root elements of the hierarchy. 
• Recursive Member: The query that refers to the CTE itself and retrieves child elements.

To create a recursive CTE, you must use a UNION ALL statement that combines the anchor and recursive members.

The recursive member must reference the CTE itself within its query definition, creating a self-referencing loop. This self-reference is essential for the recursion to occur.

Here is an example of a recursive CTE to traverse an employee hierarchy:

WITH EmployeeHierarchy AS (
    SELECT EmployeeID, FirstName, LastName, ManagerID
    FROM Employees
    WHERE ManagerID IS NULL -- Anchor member

    UNION ALL

    SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID
    FROM Employees e
    JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID -- Recursive member
)
SELECT * FROM EmployeeHierarchy;

Output:

In this example, the anchor member selects employees without managers, and the recursive member retrieves employees reporting to each manager.

Non-Recursive CTE

Non-recursive CTEs are used for simpler queries where recursion is not required. They function like regular subqueries but offer the benefit of improved code readability.

Here is an example of a non-recursive CTE.

WITH EmployeeCount AS (
    SELECT COUNT(*) AS TotalEmployees
    FROM Employees
)
SELECT * FROM EmployeeCount;

Output:

5

In this example, we define a non-recursive CTE named EmployeeCount, which calculates the total number of employees by counting the rows in the Employees table.

The result is a single-row result set containing the total number of employees.

Advantages of CTE

Using CTEs in SQL Server brings several advantages to your SQL development:

• Readability: CTEs enhance code readability by breaking down complex queries into smaller and more understandable parts, which makes your SQL code easier to read and troubleshoot. 
• Code Reusability: CTEs allow you to define a query once and reference it multiple times in your main query, which reduces code duplication and improves maintainability.
• Recursive Queries: CTEs are essential for handling recursive queries, where a query refers to its own output.
• Performance: In some cases, CTEs can improve query performance by allowing the query optimizer to optimize execution plans better.

Disadvantages of CTE

While CTEs are helpful, they do have some limitations:

• Performance: Overusing CTEs in a query can lead to performance issues. It’s essential to strike a balance between readability and performance.
• Limited Scope: CTEs are temporary, and their scope is limited to the query in which they are defined and destroyed after query execution. Also, a CTE can be only specified in a SELECT statement.
• Portability: CTEs are not supported in all database systems, so using them may limit your code’s portability.
• Keyword Restrictions: CTE members cannot use certain clauses like DISTINCT, GROUP BY, HAVING, TOP, and JOINs directly within their definition. These clauses should be applied outside the CTE when needed.
• Cannot be used in Stored Procedure Parameters: CTEs and table variables cannot be directly used as parameters in stored procedures. They are meant to be used within queries and cannot be passed as parameters like regular variables.
• Lack of Nesting: Unlike views, CTEs cannot be nested. This means you cannot define one CTE within another, as you can with views, which can be nested to create more complex query structures.
• Limited Reusability: CTEs are limited to the scope of the SQL query in which they are defined. They cannot be reused in other SQL queries, making views or temporary tables a better choice for broader reuse.
• Column Count Must Match: The number of columns specified in the CTE definition (CTE arguments) must match the number of columns in the SELECT statement when referencing the CTE. Mismatched column counts will result in an error.

FAQs

References: MSDN-CTE in SQL

You might want to read this too:

• Clustered Vs Non-Clustered Index in SQL
• Mastering SQL Inner Join
• Having VS Where Clause in SQL
• Understanding CRM Databases
• Types of Joins in SQL Server
• SQL EXISTS – Exploring EXISTS Operator in SQL Server
• Like operator in SQL
• Different ways to delete duplicate rows in SQL Server

• Author
• Recent Posts

Shekh Ali

Meet Sheikh Ali, a skilled Software engineer and passionate a blogger. He enjoys sharing his expertise in C#, Java, SQL, Design Patterns, and other related topics through his writing. When he's not coding, Sheikh Ali can often indulge in his passions for reading books and fitness training.

Latest posts by Shekh Ali (see all)

• C# Program to Capitalize the First Character of Each Word in a String - February 21, 2024
• C# Program to Find the Longest Word in a String - February 19, 2024
• How to Reverse an Array in C# with Examples - February 12, 2024