Microsoft® SQL Server 2012 Unleashed

Populating the Hierarchy

Now that you’ve created the hierarchy table, the next step is to populate it. To insert a new node into the hierarchy, you must first produce a new Hierarchyid value that represents the correct position in the hierarchy. There are two methods available with the Hierarchyid data type to do this: the HIERARCHYID::GetRoot() method and the GetDescendant method. You use the HIERARCHYID::GetRoot() method to produce the value for the root node of the hierarchy. This method simply produces a Hierarchyid value that is internally an empty binary string representing the root of the tree.

You can use the GetDescendant method to produce a value below a given parent. The GetDescendant method accepts two optional Hierarchyid input values ...