Over the years of working with databases I had to deal with recursive queries several times, first in Access, then in Oracle, then in SQL 2000. While Oracle 9i database had support for recursive queries using “CONNECT BY”, none of the other database management systems had a clean approach to execute recursive queries.
In SQL Server 2005 Microsoft added a new feature called Common Table Expressions and many people picked it up as a way to perform recursive queries.
The use case is typically for data which contains some sort of parent-child relationship where each child can be a parent as well. MSDN has an article describing how to use CTEs, please take a look.
Let’s take an Employees table which among other employee data has EmployeeId and ManagerId. Suppose we want to find all Smith’s subordinates in that table. Of course, if there are multiple people with the same last name those will get pulled as well. If Mr. Smith is the head of the company then all employees will show up at different levels, if Mr. Smith doesn’t manage anybody then only Mr. Smith will show up. To run a request like that we need:
- to create a table valued function
- define output table
- define anchor member (or first level of the recursion)
- define recursive member
- execute expression and insert data into output table
Please take a look at the code:
CREATE FUNCTION [dbo].[uft_GetEmployeeHierarchy_ByLastName]
That is it. Very simple. Please let me know if you have questions!