Inside Microsoft® SQL Server® 2008: T-SQL Querying

Materialized Path with the HIERARCHYID Data Type

SQL Server 2008 introduces a CLR-based data type called HIERARCHYID that you can use to represent graphs. This type provides a built-in implementation for the materialized path model. Like the custom materialized path model, it works ideally for trees. As with the custom model, the HIERARCHYID values provide topological ordering, positioning a node in a certain place in the tree with respect to other nodes. Besides providing topological sorting, the HIERARCHYID paths position each node under a certain path of ancestors and in a certain place with respect to siblings. The HIERARCHYID paths differ from the custom model’s paths in two main ways. First, the custom model’s paths are made of the actual ...