Hierarchyid

Hierarchyid is a built-in system data type in Microsoft SQL Server used to store the position of a node within a hierarchy. It represents the node’s location as a binary value that encodes the path in a tree, and it can be displayed as a human-readable string such as /1/2/3/. The type is designed to support efficient querying and ordered traversal of hierarchical data and can be indexed to improve performance for common operations.

The hierarchyid type provides a set of methods for path construction, navigation, and relationship checks, including GetRoot, GetDescendant, GetAncestor, GetLevel, IsDescendantOf, and ToString. These methods enable building new paths when inserting nodes, moving within a tree, and evaluating parent/ancestor relationships without resorting to recursive queries.

Typical usage involves tables that model trees with a hierarchyid column, for example a table with columns such as Id, Path of type hierarchyid not null, and Name. A clustered or nonclustered index on the Path column supports fast ordering and range queries that reflect pre-order traversal of the tree. Queries commonly sort by Path to yield hierarchical order, or use IsDescendantOf and GetAncestor to navigate relationships, while GetLevel returns the depth of a node.

Considerations include the cost of updating paths for large subtrees when nodes are moved, since descendants inherit their parent’s path. Proper indexing is important to maintain performance. hierarchyid is supported in SQL Server and Azure SQL Database and is commonly used to model organizational charts, category trees, and other hierarchical structures. It was introduced with SQL Server 2008.