New Data Types in SQL Server 2008 Part 2

HierarchyID in SQL Server 2008
Now let see how we can do this with the HierarchyID in SQL Server 2008.

First we will create a table using the new data type named HierarchyID.

CREATE TABLE [dbo].[Employee](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](40) NULL,
    [Designation] [varchar](40) NULL,
    [HierarchyID] [hierarchyid] NULL)

The HierarchyID data type is a variable length, system data type. A column of type HierarchyID does not automatically represent a tree. It is up to the application to generate and assign HierarchyID values in such a way that the desired relationship between rows is reflected in the values.

First we need to insert our root record which is the CEO of the company who is Mathew.

INSERT INTO [dbo].[Employee]
([Name]
,[Designation]
,[HierarchyID])
VALUES                     
(‘Mathew’
,’CEO’
,hierarchyid::GetRoot())

The GetRoot() method will return the root of the hierarchy tree. It will return the data type HierarchyID.

Next task is to insert the next level of employees. The next level employees are Andy, Lessa and Tony. When we do this a new method called GetDescendant is used. The GetDescendant method is very useful to get the descendant of a given node. The syntax for the method is parent.GetDescendant (child1, child2 ). The following table gives you the output that you will get depending on the parameters that you use.

Parent	Child1	Child2	Return
NULL			NULL
Not NULL	NULL	NULL	Child of parent
Not NULL	Not NULL	NULL	Child of parent greater than Child1
Not NULL	NULL	Not NULL	Child of parent less than Child2
Not NULL	Not NULL	Not NULL	Child of parent greater than child1 and less than child2.
Not NULL	Child1 or Child2 is not NULL but is not a child of parent		Exception
Not NULL	Child1 >= Child2		Exception

DECLARE @Manager hierarchyid                     
SELECT @Manager = hierarchyid::GetRoot() FROM dbo.Employee
INSERT INTO [dbo].[Employee]
([Name]
,[Designation]
,[HierarchyID])
VALUES
(‘Andy’
,’Chief Marketing Officer’
,@Manager.GetDescendant(NULL,NULL))

The above code will insert Andy into the table with HierarchyID of /1/ .

DECLARE @Manager hierarchyid                     
DECLARE @FirstChild hierarchyid                    
SELECT @Manager = hierarchyid::GetRoot() FROM dbo.Employee
SELECT @FirstChild =@Manager.GetDescendant(NULL,NULL)

INSERT INTO [dbo].[Employee]
([Name] 
,[Designation]
,[HierarchyID])
VALUES
(‘Lessa’       
,’Finance Controller’
,@Manager.GetDescendant(@FirstChild,NULL))

The above code will insert Lessa into the table with a HierarchyID of /2/. @Manager.GetDescendant(@FirstChild,NULL) will return /2/ as @FirstChild is /1/

DECLARE @Manager hierarchyid                     
DECLARE @FirstChild hierarchyid                    
DECLARE @SecondChild hierarchyid                               

SELECT @Manager = hierarchyid::GetRoot() FROM dbo.Employee
SELECT @FirstChild =@Manager.GetDescendant(NULL,NULL)
SELECT @SecondChild =@Manager.GetDescendant(@FirstChild,NULL)

INSERT INTO [dbo].[Employee]
([Name]
,[Designation]
,[HierarchyID])
VALUES
(‘Tony’        
,’CIO’
,@Manager.GetDescendant(@SecondChild,NULL))

After inserting the data, your employee table will look like the following:

This illustarted how the HierarchyID column data is inserted.

Using Stored Procedure
Even though the previous examples show how to insert data, in the real world example we would add employee using a stored procedure by passing the relevant parameters. The parameters will be Employee Name, the designation and the Manager’s employee ID.

The Following stored procedure will add an employee.

CREATE PROCEDURE [dbo].[InsertEmployee]
(@ManagerID int,
@EmpName varchar(50),
@Designation varchar(100)
)
AS
BEGIN
DECLARE @varlastChild VARCHAR(10)
,@hieManagrID HIERARCHYID
,@LastChild HIERARCHYID
—- Get the hierarchyid of the manager
SELECT @hieManagrID = HIERARCHYID
FROM Employee
WHERE ID = @ManagerID

Set @varlastChild = @hieManagrID.ToString()

—- Get the MAX hierarchyid of the next level where the employee should be in
SELECT @LastChild = MAX(HIERARCHYID)
FROM Employee
WHERE hierarchyid.ToString() LIKE @varLastChild +'[0-9]/’

INSERT INTO Employee
(Name,Designation,hierarchyid)
VALUES
(@EmpName,@Designation,@hieManagrID.GetDescendant(@LastChild,NULL))

END ;

The new method ToString() is used in the above script. This method is useful to get the string representation of the HierarchyID. The method returns a string that is a nvarchar(4000) data type. The syntax of this method is node.ToString ( ). The Parse() method is the method used to convert string into HierarchyID which is the opposite of the ToString() method.

If you run the following script, you will have a record with hierachyid of /3/1/3/.

EXEC [dbo].[InsertEmployee]
    

@ManagerID = 9,
    @EmpName = ‘Sandrina’,
    @Designation = ‘QA ‘

Data Retrieval
The next step is to retrieve your data. Let’s see how we can get the results we got from the SQL Server 2005 by using CTE.

SELECT ID
    ,Name
    ,Designation
    , Hierarchyid.GetLevel() as Level
    ,(Select ID FRom Employee Where Hierarchyid = e.Hierarchyid.GetAncestor(1)) As ManagerID
FROM employee e

In the above script we are using two new methods named, GetLevel and GetAncestor. The GetLevel() method is useful to find the Level of the current node. This method will return an integer that represents the depth of this node in the current tree. The GetAncestor() method will give you the hierarchyID of the parent node.

However, in the case of inserting a new level to a table with a HierarchyID column in not easy. You have to change all the parents using a Reparent() method. This is a very useful method which helps you to reparent a node i.e. suppose if we want to align an existing node to a new parent or any other existing parent then this method is very useful.

Syntax: node.Reparent ( oldRoot, newRoot )

Use of HierarchyID
Apart from saving organizational structure, there are several other structures that you can save with using hierarchyid in SQL Server 2008.

• A file system which has directory hierarchy
• A set of tasks in a project in which one task may be divided into several sub tasks.
• Goods which consists of other goods and raw materials.

Issues of HierarchyID Data Type

• There is no straight forward way of finding the next available Hierarchy ID. You need to find the maximum current hierarchy and then get the next available hierarchy.
• Reparent is easy and you have to reassign all the relevant records.
• You cannot have two roots in one table. Root will be displayed as /, therefore you cannot have two roots. However there are two ways to tackle this issue.
  • You can have an additional column. In the case of our previous example, you can have another column saying OrganizationID. So OrganizationID and HierarchyID make a unique key. 
  • Another way of doing this is having one root and the next level should be assigned to the organization.

However, it would be better if we could assign the root with some value, so that we can have any number of roots in the table.

]]>