Microsoft® SQL Server 2012 Unleashed

Creating a Hierarchy

First, let’s define a hierarchy in a table using the Hierarchyid data type. Instead of using a recursive CTE to perform build out the hierarchy stored in the Parts table, let’s see how to implement an alternative solution by adding a Hierarchyid column to the Parts table. First, we need to create a version of the Parts table using the Hierarchyid data type (see Listing 45.30).

LISTING 45.30 Creating the Parts Table with a Hierarchyid Data Type

Click here to view code image

Use AdventureWorks2012GoCREATE TABLE PARTS_hierarchy(     partid int NOT NULL,     hid hierarchyid not null,     lvl as hid.GetLevel() persisted,     partname varchar(30) NOT NULL,PRIMARY KEY NONCLUSTERED (partid),UNIQUE NONCLUSTERED (partname))

Note ...