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.

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.

The other option we can try common table Expression:

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

*