Recursive CTE in SQL Server

A CTE is a temporary result set introduced in SQL Server 2005 that simplifies complex queries by breaking them into smaller parts. It works with SELECT, INSERT, UPDATE, and DELETE statements and doesn’t use permanent memory.

A recursive CTE references itself to return subsets of data until all results are retrieved. It is especially useful for handling hierarchical data, like organizational charts, by joining all levels of the hierarchy.

Syntax:

WITH RECURSIVE 
cte_name [(col1, col2, ...)]
AS ( subquery )
Select col1, col2, .. from cte_name;

In the above syntax:

• cte_name: Name given to recursive subquery written in subquery block.
• col1, col2, ...colN: The name given to columns generated by subquery.
• Subquery: A MySql query that refer to itself using cte_name as its own name

Workflow of Recursive CTE

Exaplanation of Recursive CTEs:

The workflow of RECURSIVE CTE is majorly consists of these 5 steps:

• Anchor Member Execution: The CTE starts it's execution with the anchor member which is a non recursive query. It initializes the initial rows of the CTE that serves as the starting point of the recursion.

• Recursive Member Execution(Iterations): The recursive member mainly consists of the SELECT statement that references the CTE itself. Each iteration uses the results that were obtained in the previous iteration or the initial anchor member.This process is repeated until a termination condition is met.

• Termination Condition Check: The termination condition is essential for the termination of the recursive query in the CTE or else our query can get stuck in an infinite loop. This mainly consists of WHERE clause that filters the rows. When the termination condition is met the recursive member halts the execution and returns the result set.

• Union Result Sets: The UNION ALL operator combines the results from the anchor member as well as the results obtained from all of the iterations of the recursive member. At this step the final result set is prepared which will be returned to the user.

• Return Final Result: Finally the result set formed in the previous steps is returned to the user as the output generated by the recursive CTE.

Examples of Recursive CTE in SQL server:

Let us consider the following table Organization on which we want to find the Hierarchical relationships between the employees and managers of a respective organization.

Example 1: Find Hierarchical Relationship of All Levels

Query:

WITH RecursiveOrganizationCTE AS
(
    SELECT EmployeeID, FirstName, LastName, Department,ManagerID
    FROM Organization
    WHERE ManagerID IS NULL
    UNION ALL
   
    SELECT e.EmployeeID, e.FirstName, e.LastName, e.department,e.ManagerID
    FROM Organization e
    JOIN RecursiveOrganizationCTE r ON e.ManagerID = r.EmployeeID
)
--Show the records stored inside  the CTE we created above
SELECT *
FROM RecursiveOrganizationCTE;

Output:

In this example:

• The query uses a recursive CTE to extract the hierarchical structure from the Organization table.
• Managers (with managerID = NULL) appear at the top level.
• Employees who report to managers and also act as managers are shown in the middle levels.
• Employees who are not managers appear at the last level of the hierarchy.

Example 2: Simple Recursive CTE to Find Out Days in a Week

In the following example we will see how we can use the Recursive CTE in SQL server to extract all the days in a week easily.

Query:

WITH DaysofWeek(x, WeekDays) 
AS (
    SELECT 0, DATENAME(DW, 0)
    
    UNION ALL
    SELECT   x + 1, DATENAME(DW, x + 1)
    FROM  DaysofWeek  WHERE x < 6
)
SELECT WeekDays FROM DaysofWeek;

Output:

In this example:

• The query uses a recursive CTE with the DATENAME() function to list all days of the week.
• DATENAME() returns the weekday name based on its number.
• The anchor member starts with 0, which represents Monday.
• The recursive member keeps adding 1 to show the next day (Tuesday → Wednesday → … → Sunday).
• This process continues until all 7 days of the week are returned.

Example 3: Simple Recursive CTE to Find Out All the Months in a Year

In the following example we will see how we can use the Recursive CTE in SQL server to extract all the months in a year .

Query:

WITH RecursiveMonths AS (
    SELECT
        1 AS MonthNumber,
        DATENAME(MONTH, CAST('2024-01-01' AS DATE)) AS MonthName

    UNION ALL

    SELECT
        MonthNumber + 1,
        DATENAME(MONTH, DATEADD(MONTH, MonthNumber, '2024-01-01'))
    FROM RecursiveMonths
    WHERE MonthNumber < 12
)
SELECT * FROM RecursiveMonths;

Output:

In this Example:

• Query uses Recursive CTE with DATENAME() to list all months of the year.
• DATENAME() returns the month name from its number.
• Anchor member starts at 1, giving January.
• Recursive member increments monthnumber by 1 to generate months from February to December.

Example 4: Find Relationship Between Data Present in the Different Tables Through Recursive CTE.

In the following example we will see how we can use the Recursive CTE to extract meaningful data from multiple tables. Let us consider two tables CityData and CityRoutes on which we will perform our operations.

After inserting some data into the CityData Table, The Table Looks:

After inserting some data into the CityRoutes Table, The Table Looks:

Query:

WITH Destinations AS (
    SELECT
        CR.RouteID,
        CR.SourceCityID,
        CR.DestinationCityID,
        CAST(CS.CityName + ' -> ' + CD.CityName AS VARCHAR(MAX)) AS Route,
        CR.Distance
    FROM CityRoutes CR
    INNER JOIN CityData CS ON CR.SourceCityID = CS.CityID
    INNER JOIN CityData CD ON CR.DestinationCityID = CD.CityID

    UNION ALL

    SELECT
        CR.RouteID,
        R.SourceCityID,
        CR.DestinationCityID,
        CAST(R.Route + ' -> ' + CD.CityName AS VARCHAR(MAX)),
        R.Distance + CR.Distance
    FROM Destinations R
    INNER JOIN CityRoutes CR ON R.DestinationCityID = CR.SourceCityID
    INNER JOIN CityData CD ON CR.DestinationCityID = CD.CityID
    WHERE CHARINDEX('-> ' + CD.CityName, R.Route) = 0
)
SELECT Route, Distance
FROM Destinations
WHERE Route LIKE '%Bangalore%'
OPTION (MAXRECURSION 0);

Output:

In this Example :

• Uses Recursive CTE with JOINS to find the best route and total distance from a given source city.
• Anchor member: selects routes from CityRoutes joined with CityData.
• Recursive member: uses UNION ALL to add more routes by joining CityRoutes with the CTE itself.
  

Suggested Quiz

Edit Quiz

6 Questions

What is the main role of a recursive CTE in SQL Server?

• A

  It generates hierarchical results by repeatedly expanding rows until filtering stops.

• B

  It stores reusable query results that remain available for later statements.

• C

  It improves query speed by converting joins into optimized linear operations.

• D

  It structures query output into fixed segments that run independently.

Explanation:

Recursive CTEs repeatedly reference themselves to build hierarchical output.

What two components form the structure of a recursive CTE?

• A

  A header definition and a footer definition used for execution.

• B

  A seed definition and a stored definition executed later.

• C

  A block definition and an ending definition controlling scope.

• D

  An anchor definition and a recursive definition working together.

Explanation:

Recursive CTEs always begin with an anchor query followed by a self-referencing recursive query.

How does a recursive CTE normally avoid infinite execution?

• A

  Through a schema-level rule preventing repeated iterations.

• B

  Through an automatic single-step stop controlled by SQL Server.

• C

  Through a default halt applied when no results are produced.

• D

  Through a termination filter and an optional MAXRECURSION limit.

Explanation:

Recursive CTEs stop when the WHERE clause blocks further rows and MAXRECURSION restricts depth.

Why is UNION ALL commonly used in recursive CTEs?

• A

  It removes all duplicate rows to prevent repetition during recursion.

• B

  It joins anchor and recursive rows while preserving all values for recursion.

• C

  It merges results while enforcing sorting for each recursion step.

• D

  It combines recursive rows while restricting output to unique sets.

Explanation:

UNION ALL is used because recursive queries must keep all generated rows for further iterations.

What is true about the scope of a CTE?

• A

  It stays active for all later statements within the same session.

• B

  It becomes a persistent temporary table usable by other users.

• C

  It exists only for the single SQL statement in which it appears.

• D

  It is saved in storage as a view-like structure for future use.

Explanation:

A CTE is temporary and disappears immediately after the statement finishes execution.

What does using OPTION (MAXRECURSION 0) achieve?

• A

  It removes the recursion cap, allowing iterations until conditions stop.

• B

  It blocks recursion entirely by forcing an immediate return result.

• C

  It limits recursion to a single expansion before halting execution.

• D

  It restricts recursion to the default system-assigned recursion count.

Explanation:

MAXRECURSION 0 allows unlimited recursion until the termination condition stops it.

Quiz Completed Successfully

Your Score :   2/6

Accuracy :  0%

Login to View Explanation

1/6 1/6 < Previous Next >