TSQL - Recursive Queries in SQL Server

Before implement recursive queries in SQL Server, we have to understand that a recursive query is a named query expression that references itself in its definition which is presented in a hierarchical format. In SQL Server, we use Common Table Expression (CTE) feature to create recursive queries which have self-referencing capability and gives the user a simple way to search a table using iterative self-join and set operations.

We can say that the recursive query feature benefits the user by reducing the complexity of the queries and allowing a certain class of queries to execute more efficiently. Recursive queries are implemented using the WITH clause in the SQL statements.

Hence we are talking about CTE which can be used in many of the same ways we use a derived table and they can also contain references to themselves in some scenario. The primary benefits of the recursive queries to significantly reduce the amount of code required for a query that traverses recursive hierarchies.

To understand the benefits of recursive queries, we can take an example of employee hierarchy in any organization as given below -

EmpId EmpName ManagerId 101 Ryan Arjun NULL 102 Kimmy Wang 101 103 Bill White 101 104 Rosy Gray NULL 105 Neel John 102 106 Smith Bill 104 107 John Roy 105

The following example shows the semantics of the recursive CTE structure by returning a hierarchical list of employees, starting with the highest ranking employee-

SELECT EmpId, EmpName, ManagerId ---- Load Employee data into temporary data table into #Employees FROM ----- Drived table for Employees heirarchies ( SELECT EmpId=101, EmpName='Ryan Arjun', ManagerId=NULL UNION SELECT EmpId=102, EmpName='Kimmy Wang', ManagerId=101 UNION SELECT EmpId=103, EmpName='Bill White', ManagerId=101 UNION SELECT EmpId=104, EmpName='Rosy Gray', ManagerId=NULL UNION SELECT EmpId=105, EmpName='Neel John', ManagerId=102 UNION SELECT EmpId=106, EmpName='Smith Bill', ManagerId=104 UNION SELECT EmpId=107, EmpName='John Roy', ManagerId=105 )xyz EmpId EmpName ManagerId 101 Ryan Arjun NULL 102 Kimmy Wang 101 103 Bill White 101 104 Rosy Gray NULL 105 Neel John 102 106 Smith Bill 104 107 John Roy 105 ----- Create  CTE table ;WITH CTE_EMPLOYEE_HEIRARCHY AS ( SELECT E1.EmpId, E1.EmpName, E1.ManagerId, 1 AS LEVEL, ManagerName=E1.EmpName FROM #Employees E1 WHERE E1.ManagerId IS NULL UNION ALL ----- Recursive Query SELECT E1.EmpId, E1.EmpName, E1.ManagerId, LEVEL+1 AS LEVEL, ManagerName=E1.EmpName FROM #Employees E1 INNER JOIN CTE_EMPLOYEE_HEIRARCHY E3  ON E1.ManagerId = E3.EmpId  ) ----- Pull data from Recursive table or CTE SELECT EmpId, EmpName, ManagerId, LEVEL, ManagerName FROM CTE_EMPLOYEE_HEIRARCHY EmpId EmpName ManagerId ManagerName LEVEL 101 Ryan Arjun NULL Ryan Arjun 1 104 Rosy Gray NULL Rosy Gray 1 106 Smith Bill 104 Smith Bill 2 102 Kimmy Wang 101 Kimmy Wang 2 103 Bill White 101 Bill White 2 105 Neel John 102 Neel John 3 107 John Roy 105 John Roy 4

To see a demo, please see the below video from our YouTube Channel-

Structure of a Recursive CTE

Although a recursive routine in other languages returns a scalar value, a recursive CTE can return multiple rows which must be consisted of three elements:

1. Invocation of the routine is the first layer of invocation where recursive CTE consists of one or more CTE query definitions joined by UNION ALL, UNION, EXCEPT, or INTERSECT operators. Because these query definitions form the base result set of the CTE structure, they are referred to as anchor members.
2. Recursive invocation of the routine is second layer which includes one or more CTE query definitions joined by UNION ALL operators that reference the CTE itself. These query definitions are referred to as recursive members.
3. Termination check is the last layer where recursion stops when no rows are returned from the previous invocation.

References: Microsoft