January 14, 2012 at 1:29 pm
I executed the query written below and I was checking the execution plan of the query.
The second nested loop which is coasting 5% is inner join in the query. So, my question is, Top input of this join is returning only 1 row and nested loop will take this row and scan the bottom input and will output the matching rows. So, I can not see anyway it can give us 289 rows. I understand that it is treating it as self join but, it looks confusing here.
Can anybody Explain me what is actually happening here?
USE AdventureWorks
GO
WITH Emp_CTE AS (
SELECT EmployeeID, ContactID, LoginID, ManagerID, Title, BirthDate
FROM HumanResources.Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT e.EmployeeID, e.ContactID, e.LoginID, e.ManagerID, e.Title, e.BirthDate
FROM HumanResources.Employee e
INNER JOIN Emp_CTE ecte ON ecte.EmployeeID = e.ManagerID
)
SELECT *
FROM Emp_CTE
GO
Thanks...
January 16, 2012 at 1:31 am
That is a recursive CTE. You can find a detailed explanation of how your example query plan executes here:
http://blogs.msdn.com/b/craigfr/archive/2007/10/25/recursive-ctes.aspx
http://blogs.msdn.com/b/craigfr/archive/2007/11/07/recursive-ctes-continued.aspx
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply