O'Reilly logo

Microsoft SQL Server 2012 Bible by Aaron Nelson, Jose Chinchilla, Patrick LeBlanc, Jorge Segarra, Adam Jorgensen

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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” ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required