I'm looking at common uses of hierarchyID, and most examples use some sort of arbitrary numbering for the HID nodes:

HID, Name
/, World
/1/, USA
/1/1/, Texas
/1/1/1/, Houston
/1/1/2/, San Antonio
/1/2/, Virginia
/1/2/1/, Virginia Beach
/1/2/2/, Chesapeake

Here the HID is forming a unique key; a path all the way to the node.. But if I wanted to have the HID be something more like a folder, and the row be a file (so a path to the node but not including the node's id), with a separate PK, would it make (more) sense to build the HID using PKs?

ID, HID, Name
1, /, World
2, /1/, USA
3, /1/2/, Texas
4, /1/2/3/, Houston
5, /1/2/3/, San Antonio
6, /1/2/, Virginia
7, /1/2/6/, Virginia Beach
8, /1/2/6/, Chesapeake

The documentation talks about forming trees by tracking the last child node int on the parent row but if I have some arbitrary int on the parent row already (i.e. the PK), and I'm doing an "add Seattle to parent row Washington (that I've already looked up) of 12432, /1/2/, Washington then I can just place a record for e.g. 721623, /1/2/12432, Seattle

Is there a particular reason to strive to keep the HID values small/have each node an auto-increment?

  • 1
    Your HID is often referred to as Materialized Path. There are also other ways to represent the transitive closure of a tree, you can google Recursive CTE, Nested Set and Transitive Closure Table. They all have their benefits and drawbacks compared to each other, so you might want to compare them. Commented Feb 1, 2022 at 12:15

1 Answer 1


I would ask - what is the the purpose of this HID?
If it constructed from database-internal, numeric, surrogate key values, then you should never be showing them to a User anyway, so what's the purpose of assembling them in this way?

Anyway ...

The most important question to ask before deciding how to store any piece of Data is this:

How are you going to use this Data?

How are you going to "get to" this Data? (Do you need to index it?)

What are you going to do with this Data, having got it? (Do you need to further subdivide it?)

This last is interesting, because one thing you might want to do its to find an entry's parent (e.g. Seattle -> Washington). But how can you do that efficiently, armed only with the complete HID? You'd need to dissect the value, extracting the last element (which is almost always more difficult than getting the first) and then go look up the parent record.
In such a case, it might be better to hold only the parent record's id and use a recursive query to derive the HID value completely dynamically.

If your id values ever changed (which, of course, they never, ever should) then your full HIDs would be all fouled up and would have to regenerated en masse and that's painful.

  • The HID is tracking "what is inside what", something like a filesystem. The countries is perhaps a poor (well trodden) example. The actual use case is more like tracking a tree over time; We can have a warehouse that holds shelves that hold boxes that hold products. Sometimes we might move a box from shalef A to shelf B, and all those things that go with it. Sometimes we might take a box off a shelf and put it ona truck and the truck goes to another warehouse, and the box is put on the floor there (no shelves) so we'll have something we know, like box A on shelf A and move it to truck A
    – XOR cyst
    Commented Feb 1, 2022 at 14:20
  • And then later move it to warehouse B. Temporal tables watch the hierarchy changes over time. At the moment I'm exploring whether to have the ID of the item inside the hierarchy ID or not... It seems that the expectation is that it will be because a query like "get everything on shelf A" could use "is a descendent of" but only if we recombine the HID+ID to get shelf A's "full path" so to speak
    – XOR cyst
    Commented Feb 1, 2022 at 14:23
  • In terms of "getting to" this data, I will show the user a UI, perhaps like a navigable tree, or I will ask them to search for something they know will be inside a container, or I will ask them to search for a container. Perhaps they know they're shipping a vintage car - I can find the car, and it will probably be a Product, that has a ContainerId that maps directly to the Id in the table. From there I'll use the hierarchy to say "it's in ...". I can also say "along with..." and list the other products in the same container, or in any parent so I'll walk up (and down) the tree as far as needed
    – XOR cyst
    Commented Feb 1, 2022 at 18:53
  • Perhaps another class of user (delivery driver) interacting with the container has the container in front of them, and can scan a barcode on the side. Perhaps they will put that container inside another container, or they will open it and get the things out of it and put them in other containers (and they should scan every one, if it's relevant, and declare a new container for all those things). I think generally I care more about descendants than ancestors; if I need an ancestor I can pull its ID, but I'm potentially more likely to care about "everything descendant of the Nth ancestor"
    – XOR cyst
    Commented Feb 1, 2022 at 19:11
  • The temporal bit makes the tree effectively 3D, as I can say "at time X it was in ... along with..", "at time Y it was in.. along with ..". There may be cause to say "at time Z it was in ... which means it had a parent of.. which means it had a sibling of .. and that sibling had a shock sensor that registered the container being dropped, and that's how your vintage car ended up smashed, at time T, while in the care of person Z because it was in their container, in their yard
    – XOR cyst
    Commented Feb 1, 2022 at 19:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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