Materialized path pattern VS Hierarchyid

Question

I am reading the SQL server 2008 bible and it says the materialized path pattern is significantly faster then the hierarchyid. Is this really true? How can I make the hierarchyid have equal or better performance.

Answer 1

The chapter explains three methods for designing and querying hierarchies: Adjacency Pairs, Materialized Path, and HierarchyID. These are three solutions to the same problem so yes, it makes perfect sense to compare these three methods. The truth is that Materialized path is the fastest but Adjacency Pairs can solve more types of hierarchy problems. HierarchyID is clumsy, difficult to query, and, if you follow MSFT’s recommendation, it only stores the relative position, not the key, so it’s less robust.