SQL Server Nested set vs Hierarchyid performance
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?
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.