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 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 same way as a method in .NET is written. If you are not familiar with writing .NET code, methods to an object are ...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
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:
[IMAGE]Denny Cherry has over a decade of experience managing SQL Server, including MySpace.com's more than 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 uses these skills in his role as a senior database administrator and architect at Awareness Technologies. Denny is a longtime member of PASS and Quest Software's Association of SQL Server Experts and has written numerous technical articles on SQL Server management.
MEMBER FEEDBACK TO THIS TIP
Do you have comments on this tip? Let us know.
