Recursive CTE in SQL server is a common table expression that references itself.

As part of our Server administration services, Bobcares responds to all queries, large and small.

Let’s look at the specifics of recursive CTE in SQL server.

Recursive CTE in SQL server

A recursive CTE is one that refers to itself. It returns the result subset, then references itself repeatedly (recursively), stops when it returns all of the results.

The syntax of a recursive CTE is similar to that of a non-recursive CTE:


WITH RECURSIVE cte_name AS (
cte_query_definition (the anchor member)
UNION ALL
cte_query_definition (the recursive member)
)
SELECT *
FROM cte_name;


A recursive CTE is of three parts:

1. An initial query that returns the CTE’s base result set is the anchor member.
2. The recursive member is a recursive query that refers to the common table expression. Using the UNION ALL operator, the recursive member is unioned with the anchor member.
3. The recursive member’s execution is terminated by a termination condition specified in the recursive member.

A recursive CTE’s execution order is as follows:

1. Firstly, execute the anchor member to create the base result set (R0), then use this result in the next iteration.
2. Secondly, until the termination condition is met, execute the recursive member with the previous iteration’s input result set (Ri-1) and return a sub-result set (Ri).
3. Finally, use the UNION ALL operator to combine all of the result sets R0, R1,… Rn to create the final result set.

A recursive CTE’s execution is depicted in the flowchart below:

Example for Recursive CTE in SQL server

This example returns weekdays from Monday to Sunday using a recursive CTE:


WITH cte_numbers(n, weekday)
AS (
SELECT
0,
DATENAME(DW, 0)
UNION ALL
SELECT
n + 1,
DATENAME(DW, n + 1)
FROM
cte_numbers
WHERE n < 6
)
SELECT
weekday
FROM
cte_numbers;


The set of results is as follows:

The DATENAME() function in this example returns the name of the weekday based on a weekday number.

The anchor member returns the value Monday

The recursive member returns the value: next day starting from the Tuesday till Sunday.

The condition in the WHERE clause is the termination condition that stops the execution of the recursive member when n is 6

[Looking for a solution to another query? We are just a click away.]

Conclusion

To sum up, our Skilled engineers explained the recursive CTE in SQL server.