Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am playing around with HierarchyId, and I have not figured out a set based way to do the following:

  • insert all subtree at once
  • retrieve all subtree at once

This question is related to my previous one, and I suspect the only way to accomplish these two tasks with HierarchyId is one node or one level at a time. If I am using materialized path, both actions are easily accomplished by a single (and trivial) set based command.

What am I missing?

Edit: I also missed a way to move a subtree, but I learned it from Mikael Eriksson's comment

share|improve this question
Have you seen this? Moving Subtrees – Mikael Eriksson May 10 '12 at 6:31
@MikaelEriksson can you make your comment an answer? – A-K May 10 '12 at 12:34
Sure. I also elaborated a bit on how I understand what is going on. BTW, I have only tested a bit on HierarchyId, never used it in production. – Mikael Eriksson May 10 '12 at 12:55
up vote 5 down vote accepted

The function to use is GetReparentedValue but when using only GetReparentedValue the tree may end up in an "inconsistent" state.

Here is some code provided by Microsoft that takes care of that. Moving subtrees.

I guess that related to this is Enforcing a tree. It uses a calculated column for parent id that does a self join to the PK.

share|improve this answer
This is the best answer so far. Unfortunately, I do not see a way to insert/select a subtree of more than one level in one command. – A-K May 10 '12 at 13:11

Retrieving an entire subtree is simple - use the IsDescendentOf method, as per MSDN

DECLARE @Manager hierarchyid
SELECT @Manager = OrgNode FROM HumanResources.EmployeeDemo
  WHERE LoginID = 'adventure-works\dylan0'

SELECT * FROM HumanResources.EmployeeDemo
WHERE OrgNode.IsDescendantOf(@Manager) = 1

Inserting is more complex but your main issue will be with your constraints - you obviously can't insert child objects until their parent has been committed. In this case either iterate and insert in hierarchical order, or turn off constraints and insert.

When inserting a large amount of data - migration, batch or bulk insert etc - I'd turn off the constraint. When inserting operationally I'd iterate, as I've not encountered instances during the running of the system where large amounts of large need to be inserted to the hierarchy.

share|improve this answer
+1 nice to know, thanks! – A-K May 15 '12 at 15:09

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.