Click here to Skip to main content
Click here to Skip to main content

HierarchyID Data Type in SQL Server 2008

, 10 Jun 2009
Rate this:
Please Sign up or sign in to vote.
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

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

License

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

Share

About the Author

Robin_Roy
Other Brilliance Information Sdn Bhd
Malaysia Malaysia
Working as a Senior Consultant with Brilliance MSC, Malaysia.
Love to evaluate new technologies and implement the same.
Believe in sharing knowledge.

Comments and Discussions

 
GeneralNot working method Pinmemberpopnadrian8-Jun-11 23:49 

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

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

| Advertise | Privacy | Mobile
Web03 | 2.8.140826.1 | Last Updated 11 Jun 2009
Article Copyright 2009 by Robin_Roy
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid