7

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?

Vasaka
  • 579
  • 6
  • 17
  • 2
    Test, test and test? (you can also test other models like Closure) – ypercubeᵀᴹ Sep 24 '12 at 15:45
  • 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. – Vasaka Sep 24 '12 at 15:53
  • 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. – ypercubeᵀᴹ Sep 24 '12 at 15:58

1 Answers1

4

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.

callisto
  • 4,921
  • 11
  • 51
  • 92