SQL Server - How to create recursive queries with CTE (Common Table Expressions)

Hello everybody!
How are you ?

In this article I would like to demonstrate to you How to Create Recursive Queries with Common Table Expressions (CTE) in SQL Server. The CTE feature has been available since the 2005 release, and even today, many people are unaware of this interesting and useful feature in everyday life.

Introduction

A CTE is very similar to a subquery or derived table, with the advantage that the dataset can be used more than once in the query, gaining performance (in this situation) and also improving code readability. For these reasons, the use of ETC has been widespread as a replacement for other solutions cited.

Example query with CTE:

Query example with Subquery:

In addition, a CTE provides the significant advantage of being able to self-reference, thus creating a recursive CTE, which is performed repeatedly to return subsets of data until the full result is obtained.

Recursive CTE is often used to return hierarchical data, such as displaying employees on an organization chart, where they can have multiple hierarchical levels, and I will demonstrate here an example of how recursive CTE works.

Recursive CTE Examples

Example of recursive CTE using a sequence of numbers:

Result:

Example of recursive CTE using hierarchy:

Testing mass creation - CLR Corporation employees

Fictional Company Structure

Given the above structure, I want to create the hierarchy levels of these employees. How would we do this without using CTE?

Example with manual hierarchy

A very common way to create a data hierarchy in SQL Server for a few levels is through manual insertions.

Although it is quite simple (basically, Ctrl + C and Ctrl + V), when we need to automate this work or we have many levels in our hierarchy (it may even be variable), this solution ends up not being very well, leading us to look for a solution. robust.

Example of Manual Hirarchy Creation:

Result:

Example with LOOP WHILE

In order to create a more robust than the previous one that works with N levels of hierarchy, we can use a LOOP WHILE for this, which will create an iteration on each level from the anchor (first level) until the insert finds no more records to inserted.

Example of insertion using LOOP:

Result:

As you can see, I used a while loop to query the table itself, thus chaining the data and assembling the hierarchy.

But what about recursive CTE? Could she return this same result with just 1 SELECT?
A: Of course! Using this feature, we can create a query that will return the expected result with just one query.

Example with Recursive CTE

Result:

Controlling the degree of recursion with MAXRECURSION

Now that you've seen how to use recursive CTE and how it can be practical, it's time to understand the risk of building a recursive CTE in an “infinite loop”. This happens when you don't program your query's recursion well and it can end up reaching N levels and even crash your instance and consume so much resources trying to resolve the query.

To avoid this kind of situation, there is the query hint MAXRECURSION, which allows you to specify the maximum level of recursion. At this level, SQL will abort the rest of the execution and display a severely 16 error message, as shown below:

Error message:

Result:

Note that using hint OPTION (MAXRECURSION 1), only the primary level and most 1 hierarchy level were returned (1 and 2 levels). If we used the MAXRECURSION 2 value, the primary level and more 2 hierarchy levels would be returned (level 1 to level 3).

Since the exception raised by hint MAXRECURSION is of severity 16, it causes an interruption in the rest of the routine and if it is a job, this job will return a failure. If you don't want this behavior and just want to limit the level of recursion, but without causing routine error, just wrap your CTE in a block of TRY..CATCH:

Note: The hint parameter MAXRECURSION supports values ​​from 1 to 32.767. If you want to use a recursion level above this value, simply do not use this hint or specify the value 0, which means no recursion limit.

Another way to demonstrate a very interesting recursive use of CTE is to generate number sequences:

Example of recursive CTE to generate a sequence of 200 numbers:

Result:

Using this code above, it was possible to limit 200 records using hint MAXRECURSION (199) by limiting 199 recursion levels. However, you can ensure this limitation in another way (and without raising an exception):

Result:

So notice that even specifying that the recursion level is 249, that is, the maximum recursion level is 250 records (and indeed, 250 records should have been generated), as I limited the amount in 200 records inside the query. CTE, it was only possible to return the 200 records, making hint MAXRECURSION unnecessary in this case.

This showed that it is possible to limit the records of a recursive CTE (in certain situations) even without using hint MAXRECURSION.

Conclusion

In this article, I was able to demonstrate what a CTE is, in what situations we can use it, how it is built, and some of the advantages it has over other solutions, such as derived tables and subquery.

In addition, it was possible to explain how a recursive CTE works, how it can be assembled, and some examples where it can be especially useful.

I hope you enjoyed this article and that it was helpful to you!
A hug and see you next!