Using HierarchyID system data type in SQL Server 2008

In this post I would like to talk about the new system data type HierarchyID which was introduced in SQL Server 2008. I know there are samples of how to use HierarchyID in BOL,MSDN and elsewhere but I thought that it would be a good idea to give it a try with hands-on examples.It is used for storing hierarchical data.The most common scenario is that we want to store the data (relations and positions) of an organisational chart.Basically we want to store the relationship between employers and their managers.In order for you to better understand of what I am trying to do in this example, have a look at the picture below.It shows an organisational chart with the relationships between the various employees.This is the kind of data we can store in a database using the hierarchyID data type.

Let me explain the relationships in the chart above. Nick is the CEO. Jane,Mary and John report to Nick and are General Managers.Kate is the Sales Manager and reports to John.George and Diane are Marketing Managers and report to Jane. Frank is the Internet and Social Media Marketing Manager and report s to George. Gregory is the Marketing and Research coordinator and reports to Diane.

We will need an instance of SQL Server running in our machine.You can download and install the free SQL Server Express edition from here.I will use SQL Server 2008 R2 Express edition.

1) We will begin with this example. Launch SSMS and connect to the SQL Server instance.Start a new query window and type (copy-paste) the following

CREATE DATABASE Hierarchy
GO

USE Hierarchy
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Hierarchies]') AND type in (N'U'))
DROP TABLE [dbo].[Hierarchies]

CREATE TABLE Hierarchies
(
[HierarchyID] hierarchyid PRIMARY KEY CLUSTERED,
EmployeeID int UNIQUE NOT NULL,
EmployeeName varchar(20) NOT NULL,
Title varchar(20) NULL
) ;
GO

SELECT HierarchyID.ToString(), HierarchyID, EmployeeID, EmployeeName, Title
FROM Hierarchies

Execute the code above.We create a database and then we create a table. In that table one of the fields is a field of type HierarchyID.In that table we will store the data as it was explained previously in the example(chart).

2) Now let’s insert the data in the Hierarchies table.We will insert the CEO (Nick) first. In the same query window type the following.

INSERT INTO Hierarchies ([HierarchyID], EmployeeID, EmployeeName, Title)
VALUES (hierarchyid::GetRoot(), 1,'Nick','CEO')

SELECT [HierarchyID].ToString(), [HierarchyID], EmployeeID, EmployeeName, Title
FROM Hierarchies

Execute the code above. You will see that Nick was inserted as CEO in the rool level.I used the GetRoot() method to do that.

3) Now we need to insert a child node. We will insert the descendants of root node. In our case they are Jane,Mary,John who are the General Managers and report to Nick.

DECLARE @GenMgr hierarchyid
SELECT @GenMgr = [HierarchyID]FROM Hierarchies WHERE EmployeeID = 1
INSERT INTO Hierarchies ([HierarchyID], EmployeeID, EmployeeName, Title)
VALUES (@GenMgr.GetDescendant(NULL, NULL), 2, 'Jane', 'General Manager') ;

SELECT [HierarchyID].ToString(), [HierarchyID], EmployeeID, EmployeeName, Title
FROM Hierarchies

Execute the code above.You will see that Jane was inserted as a child node to the Root node Nick.As you see I use the GetDescendant() method to achieve that.

4) Now I will add the other 2 General Managers Mary and John.In the same query window type

--Insert Child (Mary)
DECLARE @Mgr hierarchyid
DECLARE @LastEmp hierarchyid

SELECT @Mgr = [HierarchyID] FROM hierarchies WHERE employeename = 'Nick'
SELECT @LastEmp = Max([HierarchyID].ToString()) FROM hierarchies WHERE [HierarchyID].GetAncestor(1) = @Mgr

INSERT INTO hierarchies ([HierarchyID], EmployeeID, EmployeeName, Title)
VALUES (@Mgr.GetDescendant(@LastEmp, NULL), 3, 'Mary', 'General Manager') ;

Go

--Insert Child (John)
DECLARE @Mgr hierarchyid
DECLARE @LastEmp hierarchyid

SELECT @Mgr = [HierarchyID] FROM hierarchies WHERE employeename = 'Nick'
SELECT @LastEmp = Max([HierarchyID].ToString()) FROM hierarchies WHERE [HierarchyID].GetAncestor(1) = @Mgr

INSERT INTO hierarchies ([HierarchyID], EmployeeID, EmployeeName, Title)
VALUES (@Mgr.GetDescendant(@LastEmp, NULL), 4, 'John', 'General Manager') ;

SELECT [HierarchyID].ToString(), [HierarchyID], EmployeeID, EmployeeName, Title
FROM Hierarchies

Execute the code above and you will see that the child nodes (John and Mary) are inserted as immediate childs of the root node Nick. Have a look at the picture below

We use the GetDescendant() method to achieve that.It returns the child node of a given parent node.We also use the GetAncestor() method which returns the ancestor of a given child node.

5) Now I will use insert George and Diane as Marketing Managers that report to Jane.In the same query window type

--Insert Child (George)
DECLARE @Mgr hierarchyid
DECLARE @LastEmp hierarchyid

SELECT @Mgr = [HierarchyID] FROM hierarchies WHERE employeename = 'Jane'
SELECT @LastEmp = Max([HierarchyID].ToString()) FROM hierarchies WHERE [HierarchyID].GetAncestor(1) = @Mgr

INSERT INTO hierarchies ([HierarchyID], EmployeeID, EmployeeName, Title)
VALUES (@Mgr.GetDescendant(@LastEmp, NULL), 5, 'George', 'Marketing Manager') ;

GO
--Insert Child (Diane)
DECLARE @Mgr hierarchyid
DECLARE @LastEmp hierarchyid

SELECT @Mgr = [HierarchyID] FROM hierarchies WHERE employeename = 'Jane'
SELECT @LastEmp = Max([HierarchyID].ToString()) FROM hierarchies WHERE [HierarchyID].GetAncestor(1) = @Mgr

INSERT INTO hierarchies  ([HierarchyID], EmployeeID, EmployeeName, Title)
VALUES (@Mgr.GetDescendant(@LastEmp, NULL), 6, 'Diane', 'Marketing Manager') ;

SELECT [HierarchyID].ToString(), [HierarchyID], EmployeeID, EmployeeName, Title
FROM Hierarchies

Execute the code above and you will see George and Diane inserted as descendants of the parent node Jane.

7) Now I will insert into the Hierarchies table Kate who is the Sales Manager who reports to John.

In the same query window type

--Insert Child (Kate)
DECLARE @Mgr hierarchyid
DECLARE @LastEmp hierarchyid

SELECT @Mgr = [HierarchyID] FROM hierarchies WHERE employeename = 'John'
SELECT @LastEmp = Max([HierarchyID].ToString()) FROM hierarchies WHERE [HierarchyID].GetAncestor(1) = @Mgr

INSERT INTO hierarchies ([HierarchyID], EmployeeID, EmployeeName, Title)
VALUES (@Mgr.GetDescendant(@LastEmp, NULL), 7, 'Kate', 'Sales Manager') ;

SELECT [HierarchyID].ToString(), [HierarchyID], EmployeeID, EmployeeName, Title
FROM Hierarchies

Execute the code above and Kate will be inserted as a child node to the parent node John.

8) Now I will insert into the Hierarchies table Frank who is Internet and Social Media Marketing Manager and reports to George.I will also add Gregory who is the Marketing and Research coordinator and reports to Diane.

--Insert Child (Frank)
DECLARE @Mgr hierarchyid
DECLARE @LastEmp hierarchyid

SELECT @Mgr = [HierarchyID] FROM hierarchies WHERE employeename = 'George'
SELECT @LastEmp = Max([HierarchyID].ToString()) FROM hierarchies WHERE [HierarchyID].GetAncestor(1) = @Mgr

INSERT INTO hierarchies ([HierarchyID], EmployeeID, EmployeeName, Title)
VALUES (@Mgr.GetDescendant(@LastEmp, NULL), 8, 'Frank', 'Internet and Social Media Marketing Manager') ;

GO

--Insert Child (Gregory)
DECLARE @Mgr hierarchyid
DECLARE @LastEmp hierarchyid

SELECT @Mgr = [HierarchyID] FROM hierarchies WHERE employeename = 'Diane'
SELECT @LastEmp = Max([HierarchyID].ToString()) FROM hierarchies WHERE [HierarchyID].GetAncestor(1) = @Mgr

INSERT INTO hierarchies ([HierarchyID], EmployeeID, EmployeeName, Title)
VALUES (@Mgr.GetDescendant(@LastEmp, NULL), 9, 'Gregory ', 'Marketing and Research') ;

SELECT HID.ToString(), HID, EmployeeID, EmployeeName, Title
FROM employee

Execute the code above and Frank will be inserted as a child node to the parent node George.Also Gregory will be inserted as a child node to the parent node Diane.
Now we have inserted all the data into the table. By all data I mean we have now the information regarding the employees and their relationships.

9) Finally I will show you how to get the direct descendants of Jane and to find the Ancenstor of Jane. In a new query window type

--To Find all direct descendants of Jane
DECLARE @Mgr hierarchyid
SELECT @Mgr = [HierarchyID] FROM hierarchies WHERE EmployeeName = 'Jane'
SELECT [HierarchyID].ToString(), * FROM hierarchies WHERE [HierarchyID].GetAncestor(1) = @Mgr

go

--To Find the Ancenstor of Jane
DECLARE @emp hierarchyid
SELECT @emp = [HierarchyID] FROM hierarchies WHERE EmployeeName = 'Jane'
SELECT * FROM hierarchies WHERE @emp.GetAncestor(1) = [HierarchyID]

Execute the code above and you will get the results.

Hope it helps!!!