SQL Server Nested set vs Hierarchyid performance

Question

I have a hierarchical data. The most common queries will be "get parent branch for node" and "get subtree of node". Updates and inserts are not likely to occur often. I am choosing between nested sets and hierarchyid. As far as I am concerned, search on nested set should be pretty fast on indexed columns, however, I have no clue about internal implementation of hierarchyid. What should I use in order to achieve highest performance possible?

Projectile

Test, test and test? (you can also test other models like Closure) – Minutely

I am asking because implementing and testing every model will cost respectable amount of time. Not to mention possible hidden drawbacks which I am not aware of. – Projectile

I suggest you flag your question for migration to DBA.SE, there is more probability to be answered there. I have no idea about the internals of HierarchyID and not many others do either, I suppose. – Minutely

Answer 1

Having used HierarchyID and self-referencing tables in different projects, I'd say HierarchyId wins hands down in terms of ease of querying.
See Querying a Hierarchical Table Using Hierarchy Methods to see how easy it can be with the built-in query methods for HierarchyID.

Suckle