Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

Part 3 - How does a recursive CTE work

Suggested Videos:
Part 1 - How to find nth highest salary in sql
Part 2 - SQL query to get organization hierarchy

A lot of you have asked to explain, how a recursive CTE work line by line. If you have not watched Part 2 already, I strongly recommend to watch that video first before proceeding.

In Part 2 of 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;

     -- Anchor
     Select EmployeeId, EmployeeName, ManagerID
     From Employees
     Where EmployeeId = @ID
     -- 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

sql server interview questions and answers

No comments:

Post a Comment

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.