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.