Wednesday, March 14, 2018

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

No comments:

Post a Comment