Menu
How does SQL Server 2008's Hierarchy data type perform compared to using the hierarchy implementation described by Joe Celko here: http://www.intelligententerprise.com/001020/celko.jhtml?
I've used Celko's method in the past with great results - but don't want to implement it for a new project unless it's better than what Microsoft has provided in SQL Server 2008.
227
Limitations of hierarchyid The hierarchyid data type has the following limitations: A column of type hierarchyid doesn't automatically represent a tree. It is up to the application to generate and assign hierarchyid values in such a way that the desired relationship between rows is reflected in the values.
The hierarchyid data type is a variable length, system data type. Use hierarchyid to represent position in a hierarchy.
SQL Server hierarchyID is a built-in data type designed to represent trees, which are the most common type of hierarchical data. Each item in a tree is called a node. In a table format, it is a row with a column of hierarchyID data type. Usually, we demonstrate hierarchies using a table design.
So far, I've only found a single reasonably interesting article on the topic. HierarchyId is much faster than any self-made self-referencing table solution:
http://www.sqlservercentral.com/articles/SQL+Server+2008/62204/
I vaguely remember seeing another comparison elsewhere (but can't find it anymore) which came to similar conclusions; using HierarchyId to build up the hierarchy is a bit cumbersome at best, but query performance is stellar.
Marc
112
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
