Search code examples
sql-serverhierarchyidtree-structure

Understanding HeirarchyId Node a little better

I am looking at HeirarchyId in SQLserver 2008 and referring to the following document:

http://www.codeproject.com/Tips/740553/Hierarchy-ID-in-SQL-Server

As far as I understand a statement like 

INSERT INTO H (Node, ID, Name)
   VALUES (HierarchyId::GetRoot(), 1, 'Thuru')

will insert a Node object into database. which has three properties, each of which are converted into one column:

  • Node text
  • Node
  • Node level

Out of which Node is containing some value which is not human readable but I think Node Text is an eye catcher and contains \1\1 kind of format which is both human readable and keeps track of each node's position in the tree. So when we say "HeirarchyId" which column does it refer to and both Node and NodeText the same exact thing except the fact that Node Text is human readable representation of Node?

Solution

  • Node is the name of the column with the data type of HierarchyID in the example. In the results of this query NodeText is a derived column that is the string representation of the path of Node column (as you say Node is not human readable).

    Child nodes will be separated by a slash for each level deep the node is - so a grand child might look like: 1/3/15

    NodeText     Node    NodeLevel     ID    Name
=============================================
/            0x      0             1     Thuru