HierarchyID Data Type in SQL Server 2008

 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;