14

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.

skaffman
  • 398,947
  • 96
  • 818
  • 769
nikmd23
  • 9,095
  • 4
  • 42
  • 57

1 Answers1

4

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • This is an interesting article, however they are not comparing to Celko's nested sets. Celko's sets are WAY better for storage than self referencing columns - which result in recursive calls. – nikmd23 Jun 26 '09 at 13:51