Storing and Retrieving Hierarchical Data in SQL SERVER using HierachyID

HierachyID datatype has been introduced in SQL Server 2008 and is still an available features today. This datatype allows you to store and retrieve data easily. It goes with several built-in CLR functions that will allow you to easily retrieve data based on your specification.






Below scripts creates an employee table with hierarchyID:

CREATE TABLE [dbo].[employee](
[employeeid] [int] IDENTITY(1,1) NOT NULL,
[hid] [hierarchyid] NULL,
[lastname] [varchar](50) NULL,
[firstname] [varchar](50) NULL,
[role] [varchar](20) NULL,
CONSTRAINT [PK_employee] PRIMARY KEY CLUSTERED
(
[employeeid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

This script will insert hierarchical data in the newly created table:

INSERT INTO [dbo].[employee]
([hid]
,[lastname]
,[firstname]
,[role])
VALUES
(‘/’,’Morris’,’Philip’,’Owner’),
(‘/1/’,’King’,’Nosmo’,’Sr. Manager’),
(‘/1/1/’,’Walking’,’Jay’,’Manager’),
(‘/1/1/1/’,’Players’,’Andy’,’Sr. web developer’),
(‘/1/1/2/’,’Trinidad’,’Victoria’,’Tester’),
(‘/2/’,’Dela Cruz’,’Juan’,’Sr.Manager’),
(‘/2/1/’,’Mills’,’Richard’,’Manager’),
(‘/2/1/1/’,’Gump’,’Richard’,’Forest’)
GO

You can now start querying for people that reports directly  to Philip Morris by using the getancestor() CLR function. Notice that the parameter is the level of relationship

select * from employee
where hid.GetAncestor(1)=’/’

Results to:

employeeid hid lastname firstname role
22 0x58 King Nosmo Sr. Manager
26 0x68 Dela Cruz Juan Sr.Manager

Hierarchy ID is stored in binary format. You can use the ToString() function to convert it to string.  This query below will return three levels of hierarchy that reports to Philip.

select [hid].ToString() as HID,hid.GetLevel()as Level,[lastname],[firstname],[role] from employee where hid.GetAncestor(1)=’/’ or hid.GetAncestor(2)=’/’ or hid.GetAncestor(3)=’/’
order by 1

This is going to be the result:

HID Level lastname firstname role
/1/ 1 King Nosmo Sr. Manager
/1/1/ 2 Walking Jay Manager
/1/1/1/ 3 Players Andy Sr. web developer
/1/1/2/ 3 Trinidad Victoria Tester
/2/ 1 Dela Cruz Juan Sr.Manager
/2/1/ 2 Mills Richard Manager
/2/1/1/ 3 Gump Richard Forest

You can also make use of getdescendant function to know who is your boss.

select [hid].ToString() as HID,hid.GetLevel()as Level,[lastname],[firstname],[role] from employee where hid.GetDescendant(null,null)=’/1/1/’

This is going to be the result of the previous query:

HID Level lastname firstname role
/1/ 1 King Nosmo Sr. Manager

 




 

Leave a Reply

Your email address will not be published. Required fields are marked *