Microsoft® SQL Server 2012 Unleashed

Using the Hierarchyid Data Type

The Hierarchyid data type introduced in SQL Server 2008 is actually a system-supplied common language runtime (CLR) user-defined type (UDT) that can be used for storing and manipulating hierarchical structures (for example, parent-child relationships) in a relational database. The Hierarchyid type is stored as a varbinary value that represents the position of the current node in the hierarchy (both in terms of parent-child position and position among siblings). You can perform manipulations on the type in Transact-SQL by invoking methods exposed by the type.