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).
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 ...
Get Microsoft® SQL Server 2012 Unleashed now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.