Microsoft® SQL Server 2008 R2 Unleashed

Creating a Hierarchy

First, let’s define a hierarchy in a table using the Hierarchyid data type. For example, this section uses the Parts table example used in Chapter 28, “Creating and Managing Stored Procedures,” to demonstrate how a stored procedure could be used to traverse a hierarchy stored in a table. There is also an example in Chapter 52 using a recursive common table expression (CTE) to perform a similar action. Let’s see how to implement an alternative solution by adding a Hierarchyid column to the Parts table. First, you create a version of the Parts table using the Hierarchyid data type (see Listing 42.14).

Listing 42.14 Creating the Parts Table with a Hierarchyid Data Type

Note the hid column defined with the Hierarchyid data ...