How does a recursive CTE work in Sql Server
How a recursive CTE work line by line.
SQL Server Interview questions and answers video series we discussed recursive CTE to retrieve the organization hierarchy.
We will be using Employees table to understand how a recursive CTE works.
sql server query to display org chart
Here is the recursive CTE that gets the organization hierarchy based on an EmployeeId. Notice that the recursive CTE has got 2 parts (Anchor & Recursive Member)
Declare @ID int;
Set @ID = 7;
WITH EmployeeCTE AS
(
— Anchor
Select EmployeeId, EmployeeName, ManagerID
From Employees
Where EmployeeId = @ID
UNION ALL
— Recursive Member
Select Employees.EmployeeId , Employees.EmployeeName, Employees.ManagerID
From Employees
JOIN EmployeeCTE
ON Employees.EmployeeId = EmployeeCTE.ManagerID
)
Select E1.EmployeeName, ISNULL(E2.EmployeeName, ‘No Boss’) as ManagerName
From EmployeeCTE E1
LEFT Join EmployeeCTE E2
ON E1.ManagerID = E2.EmployeeId
When David’s EmployeeId is passed the query produces the following output
how does a recursive cte work
Let’s now discuss how the CTE executes line by line.
Step 1: Execute the anchor part and get result R0
Step 2: Execute the recursive member using R0 as input and generate result R1
Step 3: Execute the recursive member using R1 as input and generate result R2
Step 4: Recursion goes on until the recursive member output becomes NULL
Step 5: Finally apply UNION ALL on all the results to produce the final output