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.