Tip

How to use SQL Server 2008 hierarchyid data type

SQL Server 2008 offers the new hierarchyid data type – an improved method for storing and querying data. Stored procedures can dynamically find their way up the employees' management tree but they make for complex, resource-intensive queries. With the hierarchyid data type in SQL Server 2008, a single command is all it takes to accomplish the same task. SQL Server expert Denny Cherry introduces you to the hiearchyid datatype with step-by-step examples of how to use it.

In SQL Server 2008, Microsoft has introduced its first system-defined CLR data type. The hierarchyid data type, as its name implies, is designed to store parent-child relationships between records in a single table. The classic example of this type of relationship is the employee to manager relationship that we often see defined in HR type databases. Your typical table would look something like this:

CREATE TABLE Employee
(
Employee_ID int IDENTITY (1,1),
FirstName nvarchar (50),
LastName nvarchar (50),
Supervisor_Employee_ID int,
...
)
GO

While this table structure is excellent when you need to know the employee's supervisor, it's not very helpful when you need to move further up the tree. To view the four levels up the tree, you have to write a very recursive

    Requires Free Membership to View

query. Stored procedures that can dynamically find their way up the employees' management tree are very complex and resource-intensive queries.

When using the hierarchyid data type in SQL Server 2008, a single command is all it takes to find the number of levels of management that an employee has, and with another simple command, find a manager at any level.

How to use hierarchyid in SQL Server 2008

The hierarchyid data type has many different methods. If you are familiar with writing .NET code, those methods are referenced the

More on SQL Server 2008:

 same way as a method in .NET is written. If you are not familiar with writing .NET code, methods to an object are subcomponents of the object. For example, in .NET code, a string variable has many methods -- such as substring, replace, length and so on. Methods in SQL Server are accessed in much the same way they are in .NET -- by using the "ObjectName.Method" syntax. The table below has a list of the available methods and their uses. Read on for more detailed explanations of the hierarchyid data type in SQL Server 2008, along with code examples on how to use each one.

 
Method Name Definition
GetAncestor The GetAncestor method is used to find parents and grantparents of the current record or value.
GetDescendant The GetDescendant method is used to find the child record of the given record or value.
GetLevel The GetLevel method is used to find the number of levels deep that a record or value is. This method can also be used to filter a result set to pull records above or below a certain number of levels.
GetRoot The Get Root method is used to find the root of the hierarchy tree. While an employee tree should have a single root, other uses may have more than one root. One example might be product categories which can have categories nested beneath them.
IsDescendant The IsDescendant method is used to determine if a record or value is a child of a second record or value.
Parse The parse method is used to convert the canonical version of the data type to a hierarchyid value.
Read The read method is only available within .NET CLR code. It is used to read the binary representation of the hierarchyid value.
Reparent The Reparent method is used to either verify that the current tree and a proposed to tree are valid, as well as to move the records from a current tree to a new tree.
ToString The ToString method is used to show the tree in a human readable slash (/) notated format.

Before we use these methods, we must first add what is called the root record. In a typical HR system, this would be the CEO of the company. If you were building an inventory catalog, you might find this more useful than in an HR system, as the top of each category tree would be its own root.

Let's begin by creating our table, and adding our root, which would be our CEO Frank Smith. Because Frank is the root of the tree, his insert statement is a little different from other employees because we are using the GetRoot() method to get the root value of the tree, instead of getting the value of the employees' supervisor.

CREATE TABLE Employee
(EmployeeID int IDENTITY (1,1) PRIMARY KEY,
LastName nvarchar (50),
FirstName nvarchar (50),
Title nvarchar (50),
HireDate datetime,
PhoneExtention int,
EmailAddress varchar (255),
NodeRec hierarchyid)
GO
DECLARE @NodeRec hierarchyid
set @NodeRec = hierarchyid::GetRoot() INSERT INTO Employee
(LastName, FirstName, HireDate, Title, PhoneExtention, EmailAddress, NodeRec)
values
('Smith', 'Frank', '1/17/2005', 'CEO', 65428,
'frank.smith@northwindtraders.com', @NodeRec.GetDescendant(null, null))
GO

We now have our first employee entered in the table. The next step is to enter Frank's two direct reports.

DECLARE @Manager hierarchyID, @Employee hierarchyID
SELECT @Manager = NodeRec
FROM Employee
WHERE EmployeeID = 1

SELECT @Employee = max(NodeRec)
FROM Employee
WHERE NodeRec.GetAncestor(1) = @Manager

INSERT INTO Employee
(LastName, FirstName, HireDate, Title, PhoneExtention, EmailAddress, NodeRec)
VALUES
('Jefferson', 'John', '3/23/2006', 'VP Sales', 65647,
'john.jefferson@northwindtraders.com', @Manager.GetDescendant(@Employee,
null))

SELECT @Employee = max(NodeRec)
FROM Employee
WHERE NodeRec.GetAncestor(1) = @Manager

INSERT INTO Employee
(LastName, FirstName, HireDate, Title, PhoneExtention, EmailAddress, NodeRec)
VALUES
('Karls', 'Kristin', '8/2/2006', 'VP IT', 63423,
'kristin.karls@northwindtraders.com', @Manager.GetDescendant(@Employee,
null))

We can now query the table and see the beginnings of the employee tree.

select EmployeeId, NodeRec, NodeRec.ToString()'Employee Path', LastName,
FirstName
from Employee

 
EmployeeID NodeRec Employee Path Last Name First Name Title
1 0x / Smith Frank CEO
2 0x58 /1/ Jefferson John VP Sales
3 0x68 /2/ Karls Kristin VP IT

To add employees to the table, we create a stored procedure to handle the work for us.

CREATE PROCEDURE usp_AddEmployee
@SupervisorID int,
@FirstName nvarchar (50),
@LastName nvarchar (50),
@HireDate date,
@Title nvarchar (50),
@PhoneExtention int,
@EmailAddress varchar (255)
AS
BEGIN
DECLARE @Manager hierarchyID, @Employee hierarchyID
SELECT @Manager = NodeRec
FROM Employee
WHERE EmployeeID = @SupervisorID

SELECT @Employee = max(NodeRec)
FROM Employee
WHERE NodeRec.GetAncestor(1) = @Manager

INSERT INTO Employee
(LastName, FirstName, HireDate, Title, PhoneExtention, EmailAddress,
NodeRec)
VALUES
(@LastName, @FirstName, @HireDate, @Title, @PhoneExtention,
@EmailAddress, @Manager.GetDescendant(@Employee, null))
END
GO

We can now add in some additional employees. Some will be managers and some will not.

exec usp_AddEmployee 3, 'Janice', 'Bing', '8/12/2004', 'Development Manager',
53672, 'janice.bing@northwindtraders.com'
exec usp_AddEmployee 3, 'Jim', 'Frankenfurter', '6/30/2007', 'NOC Manager',
76522, 'jim.frankerfurter@northwindtraders.com'
exec usp_AddEmployee 5, 'John', 'Willis', '7/3/2002', 'NOC Tech', 65242,
'john.willis@northwindtraders.com'
exec usp_AddEmployee 5, 'Sarah', 'Jones', '8/4/2002', 'NOC Tech', 73625,
'sarah.jones@northwindtraders.com'
exec usp_AddEmployee 5, 'Fred', 'Matt', '5/3/2004', 'NOC Tech', 26253,
'fred.matt@northwindtraders.com'
exec usp_AddEmployee 2, 'Chris', 'Marshall', '2/5/2003', 'Sales Manager',
82756, 'chris.marshall@northwindtraders.com'
exec usp_AddEmployee 8, 'Bob', 'Harris', '4/3/2003', 'Inside Sales', 63527,
'bob.harris@northwindtraders.com'

If we look at the table now we can easily see each employees path up the tree all the way to Frank Smith.

 
EmployeeID NodeRec Employee Path Last Name First Name Title
1 0x / Smith Frank CEO
2 0x58 /1/ Jefferson John VP Sales
3 0x68 /2/ Karls Kristin VP IT
4 0x6AC0 /2/1/ Bing Janice Development Manager
5 0x6B40 /2/2/ Frankenfurter Jim NOC Manager
6 0x6B56 /2/2/1/ Willis John NOC Tech
7 0x6B5A /2/2/2/ Jones Sarah NOC Tech
8 0x6B5E /2/2/3/ Matt Fred NOC Tech
9 0x5aC0 /1/1/ Marshall Chris Sales Manager
10 0x5AD6 /1/1/1/ Harris Bob Inside Sales

When looking at the canonical data, we can easily see that John reports to Jim who reports to Janice who reports to Frank. Now lets look at it programatically.

First we create a fairly simple function to identify John's manager, VP and CEO. This function will work its way up the tree until it gets to the root of the tree.

CREATE FUNCTION fn_ReturnParentTree
(@EmailAddress varchar (255))
RETURNS @Parents TABLE
(EmployeeID int, NodeRec hierarchyID, EmployeePath varchar (max),
LastName nvarchar (50), FirstName nvarchar (50), Title nvarchar (255))
AS
BEGIN
declare @NodeRec hierarchyID
DECLARE @Depth int
select @NodeRec = NodeRec
from Employee
WHERE EmailAddress = @EmailAddress

select @Depth = @NodeRec.GetLevel()

while @Depth > 0
begin
insert into @Parents
select EmployeeId, NodeRec, NodeRec.ToString()'Employee Path',
LastName, FirstName, Title
from Employee
where NodeRec = @NodeRec.GetAncestor(@Depth)

set @Depth = @Depth - 1
end

return
END
go

When we select from the function, passing it John's email address, we get back his management tree.

select *
from fn_ReturnParentTree('john.willis@northwindtraders.com')

 
EmployeeID NodeRec Employee Path Last Name First Name Title
1 0x / Smith Frank CEO
3 0x68 /2/ Karls Kristin VP IT
5 0x6B40 /2/2/ Frankenfurter Jim NOC Manager

John has been a hard worker, and Jim has decided to promote John to supervisor and have the rest of his direct reports now report to John. When we query for Jim's direct reports, we see all three NOC Techs.

select EmployeeId, NodeRec, NodeRec.ToString()'Employee Path', LastName,
FirstName, Title
from Employee
where NodeRec.GetAncestor(1) = (select NodeRec from Employee where EmployeeID
= 5)

We now want to update the other people who report to Jim, except for John, and have them report to John. We also update John's Title with this change.

Declare @OldManager hierarchyID
declare @NewManager hierarchyID
Select @NewManager = NodeRec
from Employee
Where EmployeeID = 6
select @OldManager = NodeRec
FROM Employee
Where EmployeeID = 5

UPDATE Employee
set NodeRec = NodeRec.Reparent(@OldManager, @NewManager)
where NodeRec.GetAncestor(1) = @OldManager
and NodeRec <> @NewManager
UPDATE Employee
set Title = 'NOC Supervisor'
WHERE EmployeeID = 6
Go

When we rerun the query above to find Jim's direct reports we now only find John.

select EmployeeId, NodeRec, NodeRec.ToString()'Employee Path', LastName,
FirstName, Title
from Employee
where NodeRec.GetAncestor(1) = (select NodeRec from Employee where EmployeeID
= 5)

When we query for John's second-level reports, we see Sarah and Fred.>

Some time has passed and our NOC Manager Jim has decided to leave the company. The NOC now reports to Janice. We need to update the records who report to John so that they now report to Janice. Again, we use the Reparent method, but this time we use the IsDescendant method in our where clause. We again exclude Jim from the update as he has left the company.

Declare @OldManager hierarchyID
declare @NewManager hierarchyID
select @NewManager = NodeRec
from Employee
Where EmployeeID = 4
select @OldManager = NodeRec
FROM Employee
Where EmployeeID = 5

UPDATE Employee
set NodeRec = NodeRec.Reparent(@OldManager, @NewManager)
where @OldManager.IsDescendant(NodeRec) = 1
and NodeRec <> @OldManager
go

After running this command, we see that John's canonical path has been changed from /2/2/1/ to /2/1/1/ and John's reports have been changed so that they fall within Janice's reporting tree.

 
EmployeeID NodeRec Employee Path Last Name First Name Title
4 0x6AC0 /2/1/ Bing Janice Development Manager
5 0x6B40 /2/2/ Frankenfurter Jim NOC Manager
6 0x6B56 /2/2/1/ Willis John NOC Tech
7 0x6B5A /2/2/2/ Jones Sarah NOC Tech
8 0x6B5E /2/2/3/ Matt Fred NOC Tech

At first glance, using the SQL Server 2008 hierarchyid data type may seem more complex than a standard parent/child link using the EmployeeID. But after working with the datatype, you'll recognize its full potential. When managing a large employee base -- specifically when generating organizational charts -- the hierarchyid data type will become so useful that SQL DBAs will wonder how they got along without it. All of the T-SQL shown in this tip can be found here.

ABOUT THE AUTHOR
Denny Cherry has over a decade of experience managing SQL Server, including MySpace.com's over 175-million-user installation, one of the largest in the world. Denny's areas of expertise include system architecture, performance tuning, replication and troubleshooting. He currently holds several Microsoft certifications related to SQL Server and is a Microsoft MVP.

This was first published in August 2008

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.