LATEST 🚀 Welcome to Upgradix by Shrash Studio – Your destination for free technology learning and career growth.
🕘 Updated:
MS SQL Server

Common Table Expressions (CTE) and Recursive CTE

A Common Table Expression (CTE) is a named temporary result set used inside a single SQL statement. A Recursive CTE refers to itself and repeatedly processes related rows. We use recursive CTEs for employee hierarchies, category trees and organizational structures.

Common Table Expressions (CTE) and Recursive CTE

Quick Answer: A Common Table Expression (CTE) is a named temporary result set used inside a single SQL statement. A Recursive CTE refers to itself and repeatedly processes related rows. We use recursive CTEs for employee hierarchies, category trees and organizational structures.

1. Why Do We Need Common Table Expressions?

Imagine Priya manages a company with 1,000 employees. She needs to prepare reports about managers and their reporting teams.

Every employee has an employee ID and a manager ID. Some employees manage teams, while others report to those managers.

How can Priya retrieve this information without writing deeply nested queries?

The problem without CTE

We can use nested queries to retrieve employees reporting to a particular manager. As the query grows, understanding its logic becomes harder.

Example 1: Retrieve employees using a derived table

Assume the Employees table already contains these three records.

SELECT EmployeeName, Department
FROM
(
    SELECT EmployeeName, Department
    FROM Employees
    WHERE Department = 'IT'
) AS ITEmployees
ORDER BY EmployeeName;

Expected output

EmployeeName  Department
------------  ----------
Arjun         IT
Ravi          IT

The query works, but the inner query adds another layer. Large reports can become difficult to read and maintain.

  1. Nested queries can make SQL harder to understand.
  2. Complex reports become difficult to organize.
  3. Repeated query logic can make maintenance harder.
  4. Understanding multi-level relationships needs more effort.

Now imagine Priya managing thousands of employees across many departments. She needs readable queries and clear reporting relationships.

We need a way to name intermediate query results. We also need a way to process hierarchical data.

The solution: Common Table Expressions

CTE = a named result set available within one SQL statement.

Recursive CTE = a CTE that refers to itself to process related rows.

Anchor member = the first query that returns the starting records.

Recursive member = the query that retrieves the next related records.

Priya can use a normal CTE to organize employee queries. She can use a recursive CTE to find every employee under a manager.

Animation 1: How a Recursive CTE builds an employee hierarchy

Watch Priya start with one manager and find employees across reporting levels.

Priya HR manager · Employee 101
starts with anchor member
Anchor Member Employee 101 · Priya
finds direct reports
Ravi Employee 102 · Manager 101
Arjun Employee 103 · Manager 101
repeats for new managers
Meena Employee 104 · Manager 102
Kiran Employee 105 · Manager 102
no more reports
Final Hierarchy Priya → Ravi, Arjun, Meena, Kiran
Starting and discovered records Recursive processing Final hierarchy Current animation stage

The recursive query starts with Priya. It finds Ravi and Arjun, then Ravi's team members.

2. Implement CTE and Recursive CTE in SQL Server

Let's use one Employees table for both examples.

EmployeeId = a unique employee number.

ManagerId = the employee ID of the reporting manager.

WITH = the keyword used to define a CTE.

Create a normal CTE

Priya wants a clean list of employees working in the IT department.

Example 2: Normal CTE and recursive CTE

Assume Employees contains the five records shown in the output.

WITH ITEmployees AS
(
    SELECT EmployeeId, EmployeeName, Department
    FROM Employees
    WHERE Department = 'IT'
)
SELECT EmployeeId, EmployeeName, Department
FROM ITEmployees;

WITH EmployeeHierarchy AS
(
    SELECT EmployeeId, EmployeeName, ManagerId, 0 AS Level
    FROM Employees
    WHERE EmployeeId = 101

    UNION ALL

    SELECT E.EmployeeId, E.EmployeeName, E.ManagerId,
           H.Level + 1
    FROM Employees E
    INNER JOIN EmployeeHierarchy H
        ON E.ManagerId = H.EmployeeId
)
SELECT EmployeeId, EmployeeName, ManagerId, Level
FROM EmployeeHierarchy
ORDER BY Level, EmployeeId
OPTION (MAXRECURSION 100);

Notice the first CTE creates ITEmployees. The outer query reads its result like a table.

The second CTE starts with employee 101. Its recursive member finds employees whose ManagerId matches the current EmployeeId.

UNION ALL combines the starting records and recursive results. The level increases each time another reporting level appears.

Expected output: Recursive CTE

EmployeeId  EmployeeName  ManagerId  Level
----------  ------------  ---------  -----
101         Priya         NULL       0
102         Ravi          101        1
103         Arjun         101        1
104         Meena         102        2
105         Kiran         102        2

Important: MAXRECURSION 100 limits recursive levels to 100. SQL Server's default limit is 100. Use MAXRECURSION 0 only when you have a safe termination condition.

3. Quick Summary

Normal CTE vs Recursive CTE

Differences between normal and recursive CTEs
Normal CTE Recursive CTE
Defines a named query result. Defines a query that references itself.
Does not need recursion. Uses anchor and recursive members.
Useful for readable queries and reports. Useful for hierarchical data.
Does not need a recursion limit. Uses MAXRECURSION to control recursion.

The important terms to remember

  • CTE: A named result set for one SQL statement.
  • WITH: The keyword used to define a CTE.
  • Anchor member: The starting query of a recursive CTE.
  • Recursive member: The query that finds the next related records.
  • MAXRECURSION: Controls the allowed recursive levels.

4. Common Mistakes and Best Practices

1. Forgetting the anchor member

A recursive CTE needs a starting query. Without it, the hierarchy has no starting records.

Better approach: Define the anchor member before the recursive member.

2. Creating an endless recursive query

Incorrect relationships or missing stopping conditions can cause excessive recursion.

Better approach: Check the hierarchy and set an appropriate MAXRECURSION limit.

3. Assuming a CTE is a permanent table

A CTE exists only within the statement that immediately follows it.

Better approach: Use a temporary table or permanent table when data must remain available across statements.

Design tip: Use normal CTEs to organize complex queries. Use recursive CTEs when data has a parent-child relationship. A CTE is not automatically faster than a subquery.

5. How CTE and Recursive CTE Work in Interviews

Question 1: What is a CTE in SQL Server?

What the interviewer expects: Explain its purpose and scope.

Strong answer: "A CTE is a named result set defined using WITH. I use it to make complex SQL queries easier to read. It is available only within its following statement."

Question 2: What is a Recursive CTE?

What the interviewer expects: Explain recursive processing.

Strong answer: "A Recursive CTE refers to itself. It starts with an anchor query and repeatedly processes related records. I use it to retrieve employee reporting hierarchies."

Question 3: What is MAXRECURSION?

What the interviewer expects: Explain recursion limits.

Strong answer: "MAXRECURSION controls how many recursive levels SQL Server can process. It helps prevent unexpectedly long recursive queries."

What interviewers expect at different levels

  • Junior developer: Explain CTE syntax and basic examples.
  • Senior developer: Write recursive queries and handle hierarchy data.
  • Technical architect: Discuss recursion limits, cycle handling and query performance.

6. Hands-On Practice

Beginner: School hierarchy

Create a CTE that retrieves students belonging to the Science department. Display their student IDs and names.

Expected result: A filtered list of Science students.

Intermediate: Hospital reporting hierarchy

Create a recursive CTE for Lakshmi's hospital. Start with the hospital director and retrieve all reporting staff.

Hint: Use EmployeeId and ManagerId.

Advanced: Company organization chart

Extend Priya's employee hierarchy to include multiple reporting levels.

  • Display every employee and their reporting level.
  • Include employees from different departments.
  • Identify and safely handle circular reporting relationships.

Challenge: Prevent cycles from causing repeated employee records.

7. Next Steps and Community

Previous article: Subqueries and Correlated Subqueries in SQL Server.

Next article: SQL Server Window Functions – ROW_NUMBER, RANK, DENSE_RANK and NTILE.

For more details, read the official Microsoft documentation:

Try Priya's employee hierarchy and add another reporting level. Share your recursive query and discuss different approaches with other learners.

Comments & Ratings (0)

Log in or create a student account to comment and rate this article.

Be the first to comment on this article.