How Recursive CTE Works in SQL Server

How Recursive CTE Works?

Let's create a table and insert some columns in it. 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 out how this result is obtained.

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.

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.

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

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

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.