Rob Garrett - Blogs

Today I was delighted to learn about common table expressions in SQL Server 2005. Essentially, CTEs replace the need for cursors when executing statements against rows in a result set. Here are some examples...

Let us assume that we have an employee table, and the employee table lists all employees in the company. Each employee has a unique ID - EmployeeID, which is the primary key in the table. Each employee reports to one manager and this is indicated by the managers EmployeeID in the ManagerID column for a given employee. e.g

EmployeeID	Name	ManagerID
1	Bob (CEO)	NULL
2	Sally	1
3	Jim	1
4	Paul	2
5	Simon	4

As shown above, Bob is the CEO and reports to nobody, Sally and Jim report to Bob, Paul reports to Sally, and Simon reports to Paul.

Let's say that we want to list how many employees report to each manager. We could write a query using the group by clause (assuming we don't want to join the result set back with the employees table to match the name with EmployeeID), as follows:

SELECT ManagerID, count(*) FROM #employees WHERE ManagerID IS NOT NULL GROUP BY ManagerID

What if we wanted to display those managers that had more than one person reporting to them? This result could be obtained by storing the results of the previous query in a temporary table and then running a separate query, or using a cursor. However, using a CTE it is possible to gain the same result with one statement, as follows:

WITH my_CTE (ManagerID, Employees) AS ( SELECT e.ManagerID, count(*) AS Employees FROM #employees e WHERE e.ManagerID IS NOT NULL GROUP BY e.ManagerID ) SELECT e.[Name], cte.* FROM my_CTE cte INNER JOIN #employees e ON e.EmployeeID = cte.ManagerID WHERE Employees > 1

How about listing the company hierarchy in as a tabular list, indicating rank, starting with the CEO, and following with the direct reports at each level throughout the company? A CTE is great for this problem:

WITH my_CTE (ManagerID, EmployeeID, Rank) AS ( SELECT e.ManagerID, e.EmployeeID, 0 AS Rank FROM #employees e WHERE e.ManagerID IS NULL UNION ALL SELECT e.ManagerID, e.EmployeeID, cte.Rank + 1 FROM #employees e INNER JOIN my_CTE cte ON e.ManagerID = cte.EmployeeID ) SELECT e.[Name], cte.* FROM my_CTE cte INNER JOIN #employees e ON e.EmployeeID = cte.EmployeeID

The last example uses recursion to produce the ranking. This query will work no matter how many employees work for the company and no matter how many levels of management. Prior to CTEs, obtaining the same result would have involved some sophisticated SQL.