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 

CREATE TABLE #MyEmployees
(
EmployeeID smallint NOT NULL,
FirstName nvarchar(30)  NOT NULL,
LastName  nvarchar(40) NOT NULL,
Title nvarchar(50) NOT NULL,
DeptID smallint NOT NULL,
ManagerID int NULL,
 CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC)
);

Populate the table with the following values.

INSERT INTO #MyEmployees VALUES
(1, 'HARISH', 'WARAN', N'Chief Executive Officer',16,NULL)
,(273, 'PARTHA', 'SARATHY', N'Vice President of Sales',3,1)
,(274, 'PREAM', 'KEMAR', N'North American Sales Manager',3,273)
,(275, 'VIJAY', 'KUMAR', N'Sales Representative',3,274)
,(276, N'Linda', N'Mitchell', N'Sales Representative',3,274)
,(285, N'Syed', N'Abbas', N'Pacific Sales Manager',3,273)
,(286, N'Lynn', N'Tsoflias', N'Sales Representative',3,285)
,(16, N'David',N'Bradley', N'Marketing Manager', 4, 273)
,(23, N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);

GO
WITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS
(
    SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel
    FROM #MyEmployees
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1
    FROM #MyEmployees AS e
        INNER JOIN DirectReports AS d
        ON e.ManagerID = d.EmployeeID
)
SELECT ManagerID, EmployeeID, Title, EmployeeLevel
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
   FROM #MyEmployees
   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:
   
   
   
   
2. 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”:
   

   SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1
   FROM #MyEmployees AS e
   INNER JOIN DirectReports AS d
   ON e.ManagerID = d.EmployeeID

   This is equivalent to:
   

   SELECT e.ManagerID, e.EmployeeID, e.Title
   FROM #MyEmployees E WHERE E.ManagerID=1

   Now the result set is:
   
   
   
   
3. The next input is 273, hence our query is:
   

   SELECT e.ManagerID, e.EmployeeID, e.Title
   FROM #MyEmployees E WHERE E.ManagerID=273

   The result is:
   
   
   
   
4. Thus for the next step the input will be 16,274,285.
   

   SELECT e.ManagerID, e.EmployeeID, e.Title
   FROM #MyEmployees E WHERE E.ManagerID IN(16,274,285)

   The result is:
   
   
   
   
5. Now ManagerId is 23,275,276,286.
   

   SELECT e.ManagerID, e.EmployeeID, e.Title
   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: