HierarchyID Data Type in SQL Server 2008

Introduction

One of the fantastic new features of SQL Server 2008 is the data type HierarchyID.

The HierarchyID data type allows you to construct relationships among data elements within a table, specifically to represent a position in a hierarchy.

Facts about the HierarchyId data type

Here are a few facts that you must know before starting to use HierarchyID:

1. New system CLR type supporting trees.
2. Internally stored as varbinary <= 900 bytes.
3. Holds a path that provides a topological sort of a tree.
4. Has a set of methods that provide tree functionality: GetAncestor, GetDescendant, GetLevel, GetRoot, IsDescendant, Parse, Read, Reparent, ToString, Write.
5. Efficient querying that can utilize depth-first and breadth-first indexes.

Methods provided are as follows:

• GetAncestor: Returns a HierarchyID that represents the n^th ancestor of this HierarchyID node.
• GetDescendant: Returns a child node of this HierarchyID node.
• GetLevel: Returns an integer that represents the depth of this HierarchyID node in the overall hierarchy.
• GetRoot: Returns the root HierarchyID node of this hierarchy tree. Static.
• IsDescendant: Returns true if the passed-in child node is a descendant of this HierarchyID node.
• Parse: Converts a string representation of a hierarchy to a HierarchyID value. Static.
• Reparent: Moves a node of a hierarchy to a new location within the hierarchy.
• ToString: Returns a string that contains the logical representation of this HierarchyID.

Example

CREATE TABLE dbo.Employees
(
  empid   INT NOT NULL,
  hid     HIERARCHYID NOT NULL,
  lvl AS hid.GetLevel() PERSISTED,
  empname VARCHAR(25) NOT NULL,
  salary  MONEY       NOT NULL
)
CREATE UNIQUE CLUSTERED INDEX idx_depth_first
  ON dbo.Employees(hid);
CREATE UNIQUE INDEX idx_breadth_first
  ON dbo.Employees(lvl, hid);
CREATE UNIQUE INDEX idx_empid
  ON dbo.Employees(empid);

In the above code snippet, I have created a table using HierarchyID as a data type.

CREATE PROC dbo.usp_AddEmp 
  @empid AS INT, @mgrid AS INT = NULL,
  @empname AS VARCHAR(25), @salary AS MONEY
AS

DECLARE @hid AS HIERARCHYID, @mgr_hid AS HIERARCHYID,
  @last_child_hid AS HIERARCHYID;

IF @mgrid IS NULL
  SET @hid = HIERARCHYID::GetRoot();
ELSE
BEGIN
  SET @mgr_hid = (SELECT hid FROM dbo.Employees WHERE empid = @mgrid);
  SET @last_child_hid =
    (SELECT MAX(hid) FROM dbo.Employees
     WHERE hid.GetAncestor(1) = @mgr_hid);
  SET @hid = @mgr_hid.GetDescendant(@last_child_hid, NULL);
END

INSERT INTO dbo.Employees(empid, hid, empname, salary)
  VALUES(@empid, @hid, @empname, @salary);

In the above code snippet, I have created a Stored Procedure that can be used to insert data into the table with HierarchyID as data type in one of the columns.

-- Subtree
SELECT C.*
FROM dbo.Employees AS P
  JOIN dbo.Employees AS C
    ON P.empid = 3
    AND P.hid.IsDescendant(C.hid) = 1;

-- Path
SELECT P.*
FROM dbo.Employees AS P
  JOIN dbo.Employees AS C
    ON C.empid = 14
    AND P.hid.IsDescendant(C.hid) = 1;

-- Presentation/sorting
SELECT
  REPLICATE(' | ', lvl) + empname AS empname, hid.ToString() AS path
FROM dbo.Employees
ORDER BY hid;

The above code snippet shows how to query the table with a HierarchyID column.

Conclusion

So now, with the HierarchyID data type, we can generate trees with parent-child relationships with more structured code.

Hope you enjoyed this article. Happy programming!!!