Simplifying hierarchies in SQL Server 2008

BANGALORE, INDIA: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