UPDATED - Subqueries - Recursive Queries (Part 3)

Good morning guys! Hope you're all well.

Thankfully I am back in office today! So as promised, here is an updated version of my blog post on Friday about Recursive Queries. I have been experimenting in SQL this morning and, although tricky to begin with, I think I have finally got my head around it!

Unfortunately, I'm not in the office today and I'm working from home as I sprained my ankle in the work's car park last night (ouch!) so I won't be able to post the code examples that I wanted to until I get back to work (hopefully) on Monday - sorry about that!

Today's blog post will be a continuation of the subqueries series of posts that I have been writing over the past few days and I would strongly advise that you read them first before you continue with this one. Here are the links:

• Subqueries - An Introduction (Part 1)
• Subqueries - Common Table Expressions (Part 2)

So What is a Recursive Query? 

In basic terms, a Recursive Query is a Common Table Expression which references itself. This provides multiple benefits such as being able to traverse data structures such as linked lists, returning subsets of information that can be cached and therefore making your queries perform better and run faster. Recursive queries are also known as Hierarchical Queries (as they support hierarchical data structures such as trees). 

An example of a tree data structure is shown below: 

Types of Node in a Tree Data Structure 

Root - The root node is the top of the tree. The root is always a parent as it also has children nodes come off it. 

Parent - The parent has child nodes coming off it. A Parent is almost always a child, unless it's the root node. 

Child - A child node is a descendant of a parent node. 

Leaf - A leaf node is the bottom-most child in a tree. 

What is the Structure of a Recursive Query?

A Recursive query is written with the following pattern: 

Firstly, you define the name of the Common Table Expression just like you would do with any other CTE. Next, you define what is referred to as the Anchor Member, which is a series of DML statements all joined together with either UNION, UNION ALL, EXCEPT or INTERSECT clauses. The important point to remember is that Anchor Members don't reference themselves; any self-referencing columns need to go in the Recursive Member definition (and is declared in exactly the same way as Anchor Members). You will always need to declare where the destination columns are coming from. You may or may not require a WHERE clause at the end - it is all dependent on what you are trying to achieve with your query. However if you did require one, you should place this at the end.

To finish, you will have to write a snippet of code in order to run the Recursive Query (which is just a simple SELECT statement which may or may not contain joins). Don't forget to execute the code with a GO clause at the end!   

Examples of Recursive Query

The example below shows a basic Recursive Query which displays the hierarchy as a table, where the user has an ID of 1.  In order to see the code in it's original size, please click the image: 

Recursive Queries can also be used alongside Functions and Stored Procedures so a scalar value can be taken from one of those processes and used, making the query more dynamic. 

Recursion Level

By default, the total number of recursions under one execution is 100. If your query attempts more than that, you will receive the following error: 

Msg 530, Level 16, State 1, Line 11
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

It is possible to change the level of recursion by using the OPTION MAXRECURSION clause like in the example below: 

You have to note that the minimum level for recursion is 0 and the maximum is 32767.

Final Word...

Thank you so much for reading my blog post! If you have any questions, comment or suggestions please don't hesitate to contact me or like my Facebook page SQL Genius for regular updates to my progression as a Junior Developer!