New Data Types in SQL Server 2008 Part 2

Introduction
This is the second article in the New data types in SQL Server 2008 series. We have already discussed the new date time data types in the first article. In this article, we are going to explore the new HierarchyID data type. We are going to show how to implement hierarchies in SQL Server 2005 and than how same thing in can be achived in SQL Server 2008 using the HierarchyID data type.

Hierarchies in SQL Server 2005 and Before
I am sure you are agonised with implementing data set which have a hierarchy. For example, let us assume that you want to save following organizational structure in a table.




The traditional way of implementing the above structure is for the table to have a relationiship to itself. For example we can include a column which is the EmployeID of the previous level. In this table that column is named ManagerID. ManagerID is another employee who is in the same table. The following diagram illustartes this relationship.

After inserting data for the selected organizational structure in the above table, you will have following data.

There are two common queries that you need to perform against this type of table.

1. Find out all the subordinate workers. First you need to find out the next level managers who an employee directly reports to. You then need to identify who is reporting to those managers and so on. In SQL Server 2005 you can use a CTE to achieve this.

WITH EmployeeChart (Level, Position, ManagerID, OrgLevel, SortKey)
AS (
-- Create the anchor query. This establishes the starting
-- point
SELECT a.ID
, a.Designation
, a.ManagerID
, 0
, CAST (a.ID AS VARBINARY(900))
FROM Employee a
WHERE a.Designation = 'CEO'
UNION ALL
-- Create the recursive query. This query will be executed
-- until it returns no more rows
SELECT a.ID
, a.Designation
, a.ManagerID
, b.OrgLevel+1
, CAST (b.SortKey + CAST (a.ID AS BINARY(4)) AS VARBINARY(900))
FROM Employee a INNER JOIN EmployeeChart b
ON a.ManagerID = b.Level
)

SELECT * FROM EmployeeChart ORDER BY SortKey

The above query will return all the employees that are below the position of CEO. You can see that it is not a trivial process to return the required data.

2. R-Arrange the structure when a new level is inserted into organization structure. For example:

In the above scenario, there are two things that you need to do. The first is to enter a record for the new employee.

INSERT INTO [DataTypes].[dbo].[Employee]
([Name]
,[Designation]
,[ManagerID])
VALUES
('Peter'
,'Marketing Manager'
,2)

The next step is to assign the correct manager id to next level. In this case you have update the managerid of Simon and Kevin with the employeeid of Peter.

Update Employee
Set ManagerID = (Select ID From Employee Where Name = 'Peter'
And Designation ='Marketing Manager')
Where ManagerID = (Select ID From Employee Where Name = 'Andy'
And Designation ='Chief Marketing Officer')
AND ID <> (Select ID From Employee Where Name = 'Peter'
And Designation ='Marketing Manager')

However, this is fairly simple.

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.