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?

Confabulation

Answer 1

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);

Publish

Answer 2

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

Fieldwork

Answer 3

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

Jacquelinjacqueline

I assume you're referring to recursive CTEs, as CTEs in general have nothing to do with hierarchies. Even then, these two concepts are not mutually exclusive and hierarchyid has far, far better performance than any other method I've seen (when indexed and used correctly). A hierarchyid column can also be combined with other nested-set type columns to perform very complex hierarchical queries with index-seek performance. I would say that your criticism is unfounded; hierarchyid is in fact one of the most under-used data types in SQL 2008. – Hospital

It's not just underused, it's also under-supported. There is no support for HierarchyId in LinqtoSQL or the Entity Framework (the dbml Designer just won't touch them). This is true even in the VS 2010 RC. – Edge

For some of us, HierarchyId is a much needed addition to SQL. It eliminates the need for many recursive queries, and can offer significant performance gains when used correctly. – Counsellor

For all those good folks having problems with the HierarchID via the use of ORMs and Entity Framework, wouldn't a good ol' fashioned parameterized Stored Procedure do the trick for you? And, no... it's not like you'd have to reinvent the wheel... BOL has plenty of examples that are easily convertable. – Peacetime