Some questions about HierarchyId (SQL Server 2008)

Question

I am a newbie in SQL Server 2008 and just got introduced to HierarchyId's.

I am learning from SQL Server 2008 - HIERARCHYID - PART I. So basically I am following the article line by line and while practicing in SSMS I found that for every ChildId some hexadecimal values are generated like 0x,0x58,0x5AC0 etc.

My questions are

1. What are these hexadecimal values?
2. Why are these generated and what is their use? I mean where can I use those hexa values?
3. Do we have any control over those hexa values? I mean can we update etc.
4. How to determine the hierarchy by looking into those hexa values.. I mean how can I determine which is the parent and which is the child?

Answer 1

I'll let others address your specific questions, but I will tell you, that, IMO, the HierarchyId in SQL Server 2008 isn't one of Microsoft's greatest contributions to SQL Server. They are complex and somewhat awkward. I think you will find that for many hierarchical needs, common table expressions (CTE) work great.

Randy

Answer 2

Those hex values are simply a binary representation of the hierarchy level. In general, you should not use them directly.

You may want to check out the following example, which I think should be self-explanatory. I hope it will get you going in the right direction.

Create a table with a hierarchyid field:

CREATE TABLE groups (
    group_name       nvarchar(100)  NOT NULL,
    group_hierarchy  hierarchyid    NOT NULL
);

Insert some values:

INSERT INTO groups (group_name, group_hierarchy)
VALUES
    ('root',     hierarchyid::Parse('/')),
    ('domain-a', hierarchyid::Parse('/1/')),
    ('domain-b', hierarchyid::Parse('/2/')),
    ('sub-a-1',  hierarchyid::Parse('/1/1/')),
    ('sub-a-2',  hierarchyid::Parse('/1/2/'));

Query the table:

SELECT 
    group_name,
    group_hierarchy.ToString()
FROM
    groups
WHERE
    (group_hierarchy.IsDescendantOf(hierarchyid::Parse('/1/')) = 1);

Answer 3

Adam Milazzo wrote a great article about the innards of hierarchyid here:

http://www.adammil.net/blog/view.php?id=100

In a nutshell, it's not meaningful to work with things in straight hex, but rather convert the numbers out to binary. The reason is that things are not cut up on even byte boundaries. Representing a single node can be as short as 5 bits if it's one of the first four nodes. Becomes longer and longer as more nodes are used, 6 bits each for the next 4 nodes, 7 bits each for the next 8 nodes, and then it jumps to 12 bits each for the next 64 nodes! And then up to 18 bits each for the next 1024.

I needed to convert a database to Postgres, and wrote a script which parses these hex values. You can check out a version I made for AdventureWorks here, search for "hierarchyid":

https://github.com/lorint/AdventureWorks-for-Postgres/blob/master/install.sql