Click here to Skip to main content
15,860,859 members
Articles / Database Development / SQL Server / SQL Server 2008

HierarchyID Data Type in SQL Server 2008

Rate me:
Please Sign up or sign in to vote.
4.91/5 (11 votes)
10 Jun 2009CPOL2 min read 90.3K   35   8
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)



Comments and Discussions

 
Question[My vote of 1] Not compatible with the standard Entity Framework 6.0 Pin
Robert Everett13-Jun-18 1:56
Robert Everett13-Jun-18 1:56 
QuestionExplanation of Store Procedure Pin
Member 366988926-Jul-12 0:55
Member 366988926-Jul-12 0:55 
GeneralMy vote of 1 Pin
Mahmoud yousry5-Jul-12 3:20
Mahmoud yousry5-Jul-12 3:20 
GeneralNot working method Pin
popnadrian8-Jun-11 23:49
popnadrian8-Jun-11 23:49 
GeneralMy vote of 1 Pin
PrimeCipher15-Mar-11 13:00
PrimeCipher15-Mar-11 13:00 
GeneralI'm afraid, this article is cloned from sqlmag.com Pin
arzulfi8-Jan-10 11:41
arzulfi8-Jan-10 11:41 
GeneralMy Vote of 5 Pin
johnclark6410-Aug-09 17:04
johnclark6410-Aug-09 17:04 
GeneralRe: My Vote of 5 Pin
Robin_Roy17-Aug-09 15:53
Robin_Roy17-Aug-09 15:53 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.