Microsoft SQL Server 2012 Bible

Hierarchical Data Alternatives

Although the hierarchyid is a great way to work hierarchical data, you can use alternatives where the hierarchyid is not available. Plenty of alternative methods exist, but a couple of the more popular methods are CTEs and XML.

Recursive CTE

Common Table Expression (CTE) was introduced in SQL Server 2005 as an alternative to derived tables and an expansion of temporary results sets. A CTE is defined within the execution scope of a single SELECT, INSERT, UPDATE, and DELETE statement. Much like a derived table, a CTE is not stored as an object and lasts only for the duration of the query.

CTEs can be self-referencing, enabling it to be referenced multiple times in the same query. Because it is self-referencing, it therefore creates recursion, thus a recursive CTE. A recursive CTE is one in which an initial CTE is repeatedly executed to return subsets of data until the complete result set is returned.

A recursive CTE is a common use for querying and returning hierarchical data. An example of this is the HumanResources.Employee table in which there are two columns; EmployeeID and ManagerID. The following query shows the structure of a recursive CTE by returning a hierarchical list of employees. The list starts with the highest ranking employee.

This example comes from the SQL Server 2005 AdventureWorks database because, as mentioned earlier, the Hierarchyid data type was introduced in SQL Server 2008. Thus, in order to illustrate hierarchical data “pre” ...