Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database / SQL-Server / SQL-Server-2008

HierarchyID Data Type in SQL Server 2008

4.91/5 (11 votes)
10 Jun 2009CPOL2 min read 92K  
MS SQL Server 2008 new feature, the HierarchyID data type.

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 nth 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

SQL
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.

SQL
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.

SQL
-- 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!!!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)