Recursive Common Table Expressions allow you to perform a recursive query, without the use of a resource intensive Cursor.
In this post, I’ll show you how to write a very simple Recursive CTE, and explain how it works.
The example that I’ll be working with in this post is as below:
;WITH RCTE AS
(
SELECT 1 AS [Recursion Level], 'A' [Type]
UNION ALL
SELECT [Recursion Level] +1 AS [Recursion Level], 'R' [Type]
FROM RCTE
WHERE [Recursion Level] < 5
)
SELECT * FROM RCTE
OPTION(MAXRECURSION 4)
This example obviously has no practical use, but it's good for showing how a Recursive CTE works.
The query starts in the exact same way as a normal CTE, ;WITH RCTE AS. The semicolon terminates any SQL defined before the CTE, and it's best practice to add a semicolon here for that reason. If any preceding code (Commonly the declaration of variables) isn't terminated by a semicolon, an error will occur.
The second line, SELECT 1 AS [Recursion Level], 'A' [Type] is known as the "Anchor Member". This query defines the base level item that you would like to build upon. This simple select just creates a single record with two columns.
UNION ALL joins the two parts of the recursive query together, and must be defined after your anchor query.
The query defined after the UNION ALL is known as the "Recursive Member". This query must reference the CTE in its FROM clause. This is so the query knows what base values it has, and can therefore build upon. The where clause, WHERE [Recursion Level] < 5, is a terminator statement, which stops the query from looping indefinitely. The CTE is then closed off with a closing a bracket.
A simple SELECT * FROM RCTE is all that is needed to pull back all of the required information from the CTE in this example.
OPTION(MAXRECURSION 4) is essentially another safety net to stop the query from spiralling into an infinite loop. This stops the query from running at a maximum of 4 recursions, but be aware that if 4 recursions isn't enough to extract all of the data within the loop - an error will occur. For example, If I changed my WHERE clause to less than 6 in the Recursive Member, and left my Max Recursion option at 4 I would recieve the following error:
The statement terminated. The maximum recursion 4 has been exhausted before statement completion.
The Max Recursion clause is optional.
The result set from the example query would like the below:
As you can see, 5 results were returned - even though WHERE [Recursion Level] < 5 was specified in the Recursive Member, and OPTION(MAXRECURSION 4) is defined in the outer query. This is because the Anchor Member (First record in the result set, type "A") doesn't count as a recursion. There are, however, 4 recursion records (type "R").
A more practical use of a Recursive CTE would be to build something like a family tree, or a companies employee structure.
