Microsoft SQL Server 2012 Bible

HierarchyID

The HierarchyID data type was introduced in SQL Server 2008 with the intention to provide and solve the problems surrounding working with hierarchical data. As a quick background, the HierarchyID data type is a CLR data type, but enabling the CLR to use the HierarchyID data type is not needed.

The following query joins the HumanResources.Employee table with the Person.Person table, including the column OrganizationNode as one of the columns returned in the query.

SELECT TOP 25
   e.BusinessEntityID AS BusEntID, 
   e.OrganizationNode AS OrgNode, 
   e.OrganizationLevel AS OrgLevel,
   p.FirstName + ‘ ‘ + p.LastName AS ‘Name'
FROM HumanResources.Employee e
INNER JOIN Person.Person p ON e.BusinessEntityID = p.BusinessEntityID

In the following results, notice the data returned in the OrgNode column. The OrganizationNode column in the HumanResources.Employee table is a HierarchyID data type. In its raw form, it is simply hex data.

BusEntID OrgNode OrgLevel Name -------- ------- -------- ------------ 1 0x 0 Ken Sánchez 2 0x58 1 Terri Duffy 3 0x5AC0 2 Roberto Tamburello 4 0x5AD6 3 Rob Walters 5 0x5ADA 3 Gail Erickson 6 0x5ADE 3 Jossef Goldberg 7 0x5AE1 3 Dylan Miller 8 0x5AE158 4 Diane Margheim 9 0x5AE168 4 Gigi Matthew 10 0x5AE178 4 Michael Raheem 11 0x5AE3 3 Ovidiu Cracium 12 0x5AE358 4 Thierry D'Hers 13 0x5AE368 4 Janice Galvin 14 0x5AE5 3 Michael Sullivan 15 0x5AE7 3 Sharon Salavaria 16 0x68 1 David Bradley 17 0x6AC0 2 Kevin Brown 18 0x6B40 2 John Wood 19 0x6BC0 2 Mary Dempsey 20 ...