Programming Microsoft® SQL Server™ 2008

The hierarchyid Data Type

The new hierarchyid data type in SQL Server 2008 enables a robust hierarchical structure over a relational table that can relieve you of much of this maintenance burden. As with the self-joining approach, each row in the table represents a node in the tree. However, instead of encoding just the parent value in a foreign key column, each row now has a unique hierarchyid value that describes the full path all the way from the root of the tree down to that row’s node position within the tree. The hierarchyid value in each row therefore identifies each node’s location in the tree with respect to its parent, sibling, and child nodes (which are simply other rows in the table with their own hierarchyid value). In this manner, ...