I tuned into SQL Down Under again this week and this time Greg Low was interviewing Dr. Tom Moreau. One of the key take aways from me is a new feature in SQL Server 2005 called Common Table Expressions.
To me – CTE’s are kind of like a dynamic view that you create inline in your SQL query which can simplify the queries that immediately follow it. However, as Greg and Tom pointed out on the podcast they can be used to create recursive queries over the top of hierarchies that are flattened into a relational table.
A good example of this works actually comes from the SQL Books Online:
USE
AdventureWorks;
GO
WITH
DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS
(
SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
FROM HumanResources.Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
FROM HumanResources.Employee e
INNER JOIN DirectReports d
ON e.ManagerID = d.EmployeeID
)
SELECT
ManagerID, EmployeeID, EmployeeLevel
FROM
DirectReports ;
GO
In the query above we have created a CTE with the “WITH DirectReports” block and you can see that it refers to itself inside that block. It does an initial query to find the root of the employee hierarchy by finding the person that doesn’t have an associated manager and then unions another query onto the end of it which returns a list of direct reports (referencing itself).
This is going to be a very useful tool and will avoid having to use all kinds of hacks to support efficient querying of hierarchies (code wise) – having said that I don’t know how it stacks up to other approaches performance wise.
Another good show Greg!