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.