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?

Thanks in advance

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

Daniel's example is very elegant and good for an immediate understanding of how the hierarchyid datatype in SQL Server is used/manipulated. However, a gentle reminder for any user trying out the piece of code by copying and pasting on to a query window is to make sure that the user replace the " (double-quotes) with ' (single quote) and the '\' (backslash) with a '/' (forward-slash) before executing it (otherwise one would get errors!). The limitations of the web UI to express the syntax correctly is what one needs to remember.