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.
A:
Do you mean materialized path vs. nested sets or ? A materialized path pattern can employ a hiearchyid datatype. It doesn't make sense to compare the speed of a pattern vs a datatype.
Greg
2010-04-23 18:53:03
Yes, in the sql server 2008 bible - they compare the materialized path pattern and the hierarchyid. Even though the hierarchyid uses the materialized path it does not fully utilize all of its features. So the author compared them side by side and he said the materialized path is much more performant then the hierarchyid. I don't understand how this can be!
Luke101
2010-04-23 22:50:29
+3
A:
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 true 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. –Paul Nielsen www.sqlserverbible.com
Paul Nielsen
2010-04-24 00:33:21