It would be great if the advantages of storing data using HierarchyID vs. older e.g., storing EmpID, MgrID in the same table for Emp-Mgr hierarchy methods be discussed in the next article.

Balaji7/26/2008 5:46:37 AM

Is Hierarchyid a field or a data type. It appears in your SQL Scripts that hierarchyid is a field you are using. If I am correct doesn't a data type determine what type the database field is?

Forgive me if this is a stupid question

[Itzik: HIERARCHYID is a datatype. You can define columns, parameters and variables of this datatype. I like to think of a datatype as a class, and of a value of the type as an object (instantiation of the class). That is, data and behavior supporting it.]

Steven7/17/2008 9:08:21 AM

Hi Charles!

The following is Itzik Ben-Gan's response your question:

“Hi Charles,

The documentation doesn’t elaborate on how exactly the logical string representation of the hierarchyid value should be interpreted.
The important thing is that the value itself positions the node in a certain place in the tree, includes info on all ancestors, and positions the node in a certain place among siblings.
We can try to figure out from observation and experimentation what the logical representation means.

The obvious part is that a forward slash is used as a separator between levels.
As for the values within a level, it’s a bit more tricky than simply ordinal positions.
If you provide the GetDescendants method two NULLs as input, you get 1 within the level:

select cast('/' as hierarchyid).GetDescendant(null, null).ToString(); -- /1/

If you provide an integer x as the left input and NULL as the right input, you get the left integer plus 1 (x + 1):

select cast('/' as hierarchyid).GetDescendant('/1/', null).ToString(); -- /2/
select cast('/' as hierarchyid).GetDescendant('/9/', null).ToString(); -- /10/

However, if you provide two consecutive integers within the level, x and x+1, as the left and right inputs, you get the left with a dot and an integer 1 after the dot (x.1):

select cast('/' as hierarchyid).GetDescendant('/1/', '/2/').ToString(); -- /1.1/

If you provide two consecutive values in the form x.y, x.y+1, you get x.y.1:

select cast('/' as hierarchyid).GetDescendant('/1.1/', '/1.2/').ToString(); -- /1.1.1/

Cheers,
Itzik”

Please let me know if you have any more questions.
Thanks!

Megan Bearly
Associate Editor, SQL Server Magazine
mbearly@sqlmag.com

meganbearly 7/14/2008 2:16:58 PM

There is no explanation of what the notation /2/1/1/2/ stands for. I can only guess that the numbers represent the ordinal position among siblings at that level. Is that so? The article doesn't say. Otherwise it's a good article. Respond to: charles@kangai.demon.co.uk.

Charles7/9/2008 5:29:10 AM