Programming Microsoft® SQL Server® 2012

The hierarchyid Data Type

The hierarchyid data type enables a robust hierarchical structure over a relational table that can relieve you from much of this maintenance burden. Like the self-joining approach, each row in the table represents a node in the tree. However, instead of storing just the parent value in a foreign key column, each row now has a unique hierarchyid whose encoded value 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, relative to the hierarchyid values of its neighboring parent, sibling, and child nodes that are stored in other rows. In this manner, the hierarchyid values link ...