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! 

Good afternoon everyone!

Today's blog post is a continuation of the blog I started yesterday on Subqueries (an Introduction) which I would advise on reading before you continue reading this blog post. I am going to discuss Derived Tables and Common Table Expressions and show how important they are in creating more complex database queries.

What is a Derived Table? 

A Derived Table is a locally named table expression (which means that it can only be visible by the statement that created it in the first place). Derived Tables can be used as a substitute to creating temporary tables and views as they can be created on the fly and can also be reused. This is the more preferred method to having to define all new tables and populate them, select all the data contents and then have to repeat for every instance that the view or table needs to be used. An example of a Derived Table in action is below:

The equivalent of this using a Temporary Table is shown below:

Also you need to remember to DROP your temporary table after you've finished using it as, although it won't appear in the Table List in the Object Explorer, it will still exist in the database with an old snapshot of data from when it was created:

So as you can tell, Derived tables aid code re-usability, plus they can provide better performance than Temporary Tables.

What is a Common Table Expression? 

A Common Table Expression is also a locally named table expression of 3 main parts, however these parts occur in a different order to a Derived Table. The main benefit of a CTE is that it can be reused over and over again. They are also much more readable than derived tables, making them much easier to debug when you come across a problem. An example of a Common Table Expression is shown below, which should produce exactly the same results as the other 2 examples of code I have included here: 

Next...

As you can tell, this is a very basic overview to cement my understanding. As time goes on, I may come back to this post and expand further but for now, an awareness of what they are and how they can be used is probably all I will need to know. Within my next few blog posts, I will be discussing recursive queries and how they can be used within your database solutions. Again, I just want to reiterate my thanks for the continuing support of my blog and my studies. If you have any comments, questions, suggestions or feedback please don't hesitate to post in the comment box below or like my page SQL Genius on Facebook.

Good afternoon everyone! Today's blog post will be an introduction to the use of Subqueries in SQL Server. I have seen their use around projects that we have done in the workplace and they are actually quite flexible (though you have to be careful where you use them as they can reduce the speed and performance of the entire query).

Due to the nature of subqueries - I will start off with a brief introduction in this blog post and then explain in more detail about their uses within more complex systems over the coming days (stay tuned!)

So What Are Subqueries? 

Subqueries are basically queries that can return scalar values, multiple values or even a table of results nested within each other (with a maximum of 32 queries nested together). You may hear subqueries referred to as inner queries (with an outer query encapsulating) or as an inner SELECT. They can be used within any DML statement (SELECT, UPDATE, DELETE and INSERT) though they must contain a SELECT and a FROM clause which is declared within parentheses. What should also be noted is that they can be placed within the DML statement, either in the FROM clause or the WHERE clause, making them incredibly flexible.

There are 2 main types of subquery, Self-Contained (or Non-Correlated) and Correlated, which I am going to discuss below:

Self-Contained 

Self-Contained Queries are queries that do not rely on the outer query in order to bring back results. So basically, if you take everything inside the parentheses and execute it as a different query it would also bring back its own result set.

The example below shows a Self-Contained query that brings back the ItemId, ItemName and Price of the lowest priced Item in the Item table. You will be able to run both ends of the query and it will still bring back a result set:

Correlated

Correlated Queries, unlike Self-Contained queries, do rely on the outer query in order to bring back results, otherwise you will receive some form of error when trying to execute the inner query on its own. This is because, although it may not look like it at a first glance, there is actually a join within the subquery linking it to information from the outer query.

The example below shows a Correlated query that brings back all columns from the Customer table where a customer hasn't made an order (as the CustomerId won't feature in the CustomerItem Table):







An important thing to note is that you don't always use joins when linking tables together as you may simply wish to join two instances of the same table. You also don't always need to use the JOIN statement to create a join (as in the example above) however I prefer using them as it can make the code more readable.

Next...

Over the next coming days, I will explain more complex uses of subqueries, such as in Table Expressions where subqueries return result sets that have to be named and relational. These make excellent building blocks for more complicated queries as subqueries can be reused over and over again without having to be copied and pasted.

Also, I want to thank you all so much for reading and following my SQL-Genius blog, I really appreciate your support! If you have any comments, feedback or questions you wish to ask me please don't hesitate to message me in one of my comment boxes, or follow me on Facebook for regular updates.