How Recursive CTE Works in SQL Server

How Recursive CTE Works
  1. Let's create a table and insert some columns in it
  2. Write a simple Recursive CTE 
  1. CREATE TABLE #MyEmployees  
  2. (  
  3. EmployeeID smallint NOT NULL,  
  4. FirstName nvarchar(30)  NOT NULL,  
  5. LastName  nvarchar(40) NOT NULL,  
  6. Title nvarchar(50) NOT NULL,  
  7. DeptID smallint NOT NULL,  
  8. ManagerID int NULL,  
  10. );  
Populate the table with the following values.
  1. INSERT INTO #MyEmployees VALUES   
  2. (1, 'HARISH''WARAN', N'Chief Executive Officer',16,NULL)  
  3. ,(273, 'PARTHA''SARATHY', N'Vice President of Sales',3,1)  
  4. ,(274, 'PREAM''KEMAR', N'North American Sales Manager',3,273)  
  5. ,(275, 'VIJAY''KUMAR', N'Sales Representative',3,274)  
  6. ,(276, N'Linda', N'Mitchell', N'Sales Representative',3,274)  
  7. ,(285, N'Syed', N'Abbas', N'Pacific Sales Manager',3,273)  
  8. ,(286, N'Lynn', N'Tsoflias', N'Sales Representative',3,285)  
  9. ,(16, N'David',N'Bradley', N'Marketing Manager', 4, 273)  
  10. ,(23, N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);  
  1. GO  
  2. WITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS   
  3. (  
  4.     SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel  
  5.     FROM #MyEmployees   
  6.     WHERE ManagerID IS NULL  
  7.     UNION ALL  
  8.     SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1  
  9.     FROM #MyEmployees AS e  
  10.         INNER JOIN DirectReports AS d  
  11.         ON e.ManagerID = d.EmployeeID   
  12. )  
  13. SELECT ManagerID, EmployeeID, Title, EmployeeLevel   
  14. FROM DirectReports  
*=> The result of “SELECT ManagerID, EmployeeID, Title, EmployeeLevel FROM DirectReports” is:
Now let's find how this result is obtained. 
    1. SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel  
    2. FROM #MyEmployees   
    3. WHERE ManagerID IS NULL  
    This is the base result set of the CTE structure; they are referred to as anchor members. And the result is:
    manager ID
  1. This is the input for the next recursive operation (in other words ManagerID = d.EmployeeID). Here EmployeeID is “1” and the input to the next step is “1”:
    1. SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1  
    2. FROM #MyEmployees AS e  
    3. INNER JOIN DirectReports AS d  
    4. ON e.ManagerID = d.EmployeeID  
    This is equivalent to:
    1. SELECT e.ManagerID, e.EmployeeID, e.Title  
    2. FROM #MyEmployees E WHERE E.ManagerID=1  
    Now the result set is:
    query output
  2. The next input is 273, hence our query is:
    1. SELECT e.ManagerID, e.EmployeeID, e.Title  
    2. FROM #MyEmployees E WHERE E.ManagerID=273  
    The result is:
  3. Thus for the next step, the input will be 16,274,285.
    1. SELECT e.ManagerID, e.EmployeeID, e.Title  
    2. FROM #MyEmployees E WHERE E.ManagerID IN(16,274,285)  
    The result is:
    query result
  4. Now ManagerId is 23,275,276,286.
    1. SELECT e.ManagerID, e.EmployeeID, e.Title  
    2. FROM #MyEmployees E WHERE E.ManagerID IN (23,275,276,286)  
    Now the result set is empty.
    When the result set is empty the recursion will stop and return the result in a union manner. Thus the final result is:
    result set is empty