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.