SQL Server 2008 Hierarchy Data Type Performance?

hierarchy, performance, sql, sql-server

Solution

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

Problem

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.

Original source