0

I'm implementing a organizational chart in SQL Server 2016 as a hierarchical structure. I have to do read, insert, update on the tree. I also have to get the subtree faster.

I looked at few different ways to implement this. The first and foremost, I thought about hierarchyid. Then I came across a couple of different ways that Joe Celko mentioned in his book SQL Anti-Patterns enter image description here

I also see another method mentioned at http://www.sqlservercentral.com/articles/Hierarchy/94040/.

My questions are:

  1. Doesn't hierarchyid do what a closure table does?
  2. Does using hierarchyid make query slower?
wonderful world
  • 10,969
  • 20
  • 97
  • 194
  • I personally like hierarchyid and if that's the "path enumeration" line item in the provided chart, it absolutely can do referential integrity. And for ancestor/descendent type queries given a specific element, it's pretty fast. What issues are you seeing? – Ben Thul Jul 18 '16 at 19:37
  • I'm looking for the existing feature of SQL and don't re-invent a new structure. So, I think HierarchId will work. Do you know whether I can use hierarchyid to create a graph like this one http://stackoverflow.com/questions/6397641/converting-directed-acyclic-graph-dag-to-tree/6486793? – wonderful world Jul 20 '16 at 09:15

0 Answers0