SQL Server Recursion / CTE

SQL Server Recursion / CTE
When we think about recursion, while loop comes to my mind. But common table expression has been in the SQL Server for a while now and it makes recursion queriers more efficient
Employee Table Example. Given EmployeeID, we have to select the entire hierarchy.
— Create an Employee table.

CREATE TABLE dbo.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 values.
INSERT INTO dbo.MyEmployees
VALUES (1, N'Ken', N'S?nchez', N'Chief Executive Officer', 16, NULL),
(273, N'Brian', N'Welcker', N'Vice President of Sales', 3, 1),
(274, N'Stephen', N'Jiang', N'North American Sales Manager', 3, 273),
(275, N'Michael', N'Blythe', 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);
Lets pick an employee and figure out the hierarchy:

Steps:
1. Retrieve the Employee Details.
2. Retrieve his Managers Details.
3. Swap Employee with Manager and Repeat Step 1 and Step 2.
We can do retrieve the data through traditional while loop.

DECLARE @pEmployeeID AS INT;
SET @pEmployeeID = 23;
WHILE @pEmployeeID IS NOT NULL
BEGIN
-- select employee information
SELECT e.EmployeeID,
e.ManagerID,
e.Title,
e.FirstName,
e.LastName
FROM dbo.MyEmployees AS e
WHERE e.EmployeeID = @pEmployeeID
UNION
-- select manager information
SELECT m.EmployeeID,
m.ManagerID,
m.Title,
e.FirstName,
e.LastName
FROM dbo.MyEmployees AS e
INNER JOIN
dbo.MyEmployees AS m
ON e.ManagerID = m.EmployeeID
WHERE e.EmployeeID = @pEmployeeID;
-- swap manager with Employee and repeat
SELECT @pEmployeeID = m.ManagerID
FROM dbo.MyEmployees AS e
INNER JOIN
dbo.MyEmployees AS m
ON e.ManagerID = m.EmployeeID
WHERE e.EmployeeID = @pEmployeeID;
IF (@pEmployeeID IS NULL
OR @pEmployeeID = 1)
BREAK;
END

The other option we can try common table Expression:

DECLARE @pEmployeeID AS INT;
SET @pEmployeeID = 285;
WITH cte_employee (EmployeeID, ManagerID, Title, FirstName, LastName)
AS (-- DEFINE THE ANCHOR
SELECT e.EmployeeID,
e.ManagerID,
e.Title,
e.FirstName,
e.LastName
FROM dbo.MyEmployees AS e
WHERE e.EmployeeID = @pEmployeeID
UNION ALL
-- RECURSION ELEMENT
SELECT m.EmployeeID,
m.ManagerID,
m.Title,
m.FirstName,
m.LastName
FROM cte_employee AS e
INNER JOIN
dbo.MyEmployees AS m
ON e.ManagerID = m.EmployeeID)
-- SELECT THE FINAL LIST
SELECT *
FROM cte_employee AS a;

Doing the STATISTICS IO on both, CTE Performs better than the previous while loop.
SET STATISTICS IO ON;

For while loop:
(2 row(s) affected)
Table ‘MyEmployees’. Scan count 0, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘MyEmployees’. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(2 row(s) affected)
Table ‘MyEmployees’. Scan count 0, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ‘MyEmployees’. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

For CTE:
(3 row(s) affected)

Table ‘Worktable’. Scan count 2, logical reads 20, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘MyEmployees’. Scan count 0, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.