Simplifying Hierarchies in SQL Server 2008

There are enough business situations where you need to represent hierarchies

in your data driven applications. One of the easiest hierarchies is the typical

organization chart where employees report to their managers, the managers report

to general managers and so on. There are several ways of representing

hierarchies in data driven applications, the most common is to use XML and

self-referencing tables. Microsoft SQL Server 2008 introduces a new way to store

hierarchies using a new new built-in data type and set of functions. Let us

start exploring with an example.

Storing hierarchies



Let us create a table 'Employee' to store the hierarchy shown in the figure.

USE payroll



GO


CREATE TABLE Employee


(


NodeID hierarchyid,


EmployeeID int primary key,


EmployeeName nvarchar(20)


)

The new data type is hierarchyid, which shall be explained later. We begin by

inserting our first row for Anil. He will be the root of the tree structure that

we create.

Inserting Root



Type the following lines of code:

INSERT INTO Employee



VALUES(hierarchyid::GetRoot(), 3, 'Anil')

GetRoot is one of the new set of hierarchy functions introduced by SQL Server

2008. It returns the root of the hierarchy tree. The special double colon (::)

syntax is used when calling methods on the data type itself, similar to a static

method call in object oriented languages.

Let us now try to demystify the new data type hierarchyid by querying the

table.

SELECT * FROM Employee

This kind of a hexadecimal representation may not be immediately beneficial.

Let us add the next few nodes to the hierarchy and then we will use some

functions to present the node ID in a more user-friendly way.

Inserting the First Level



Let us insert the record for Amar next. This is not the root of the

hierarchy and inserting this record would require some effort.

DECLARE @Manager hierarchyid



SELECT @Manager = NodeID FROM Employee


WHERE EmployeeID = 3

INSERT INTO Employee



VALUES(@Manager.GetDescendant(NULL, NULL), 2, 'Amar')

The code above essentially conveys that Amar would be the descendant from

Anil (employee id 3). The two NULL parameters shall be explained later.



We will insert Ajit next, as a descendant to Anil. We will insert him after Amar
(employee id 2), ie between Amar and, well, nothing.

DECLARE @Manager hierarchyid



DECLARE @Sibling hierarchyid

SELECT @Manager = NodeID FROM Employee



WHERE EmployeeID = 3

SELECT @Sibling = NodeID FROM Employee



WHERE EmployeeID = 2


-- This variable represents Amar

INSERT INTO Employee



VALUES(@Manager.GetDescendant(@Sibling, NULL), 1, 'Ajit')

The parameters to GetDescendant are the two nodes between which we want to

insert the new node.


Let us now check the table data.

SELECT * FROM Employee

The hexadecimal output is unhelpful. Let me add a function to the query.



SELECT NodeID, NodeID.GetLevel() AS NodeLevel, EmployeeID, EmployeeName FROM
Employee

The GetLevel() function as you would have guessed, returns the level of the

node. Let us add nodes at the next level.

Inserting the Second Level



To insert the second level of hierarchy write the following lines of code:

DECLARE @Manager hierarchyid



DECLARE @Sibling hierarchyid


SELECT @Manager = NodeID FROM Employee


WHERE EmployeeID = 1


-- This variable represents Ajit

INSERT INTO Employee



VALUES(@Manager. GetDescendant(NULL,NULL), 4, 'Abhijit')

Let us add Anand next.



DECLARE @Manager hierarchyid


DECLARE @Sibling hierarchyid

SELECT @Manager = NodeID FROM Employee



WHERE EmployeeID = 1


-- This variable represents Ajit

SELECT @Sibling = NodeID FROM Employee



WHERE EmployeeID = 4


-- This variable represents Abhijit

INSERT INTO Employee



VALUES(@Manager.GetDescendant(@Sibling, NULL), 5, 'Anand')

Let us now query and check if the levels are as we

desire.

SELECT NodeID, NodeID.GetLevel() AS NodeLevel,

EmployeeID, EmployeeName FROM Employee

Let me introduce another useful function with hierarchies.

SELECT NodeID, NodeID.GetLevel() AS NodeLevel,

NodeID.ToString() AS NodeLocation,



EmployeeID, EmployeeName FROM Employee


ORDER BY NodeLocation ASC

If you have some experience with the Windows folder hierarchy, it would be

easy for you to understand the output of the ToString() function as applied to

the hierarchyid data type. I leave adding the last level to you as an exercise.

Conclusion



Hierarchies can get much more complex than what has been demonstrated. I

would encourage you to refer to online books for a thorough understanding on the

usage of hierarchies.



You would also need to learn when to use this new approach as opposed to storing
hierarchies in XML and in self-referencing tables.

Amaresh Patnaik