Recursive CTE in SQL Server

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.