LogoASPAlliance: Articles, reviews, and samples for .NET Developers
SQL Server 2008 New Features - Hierarchy Data Type
by Nidal Arabi
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 18415/ 24


As the current business world become more complex, a more complex data type in RDMS is needed. Well, Microsoft SQL server has provided a new data type that would store data of tree charts data type in a well organized manner.

About The Feature

This feature allows you to digitize organization chart into a Microsoft SQL Server data type. It will also provide the developers with methods to manipulate the data insertion as well as data retrieval from the table.


In order to apply the article procedures, you should have installed any version of Microsoft SQL Server 2008 (Express, Standard, Developer, or Enterprise) and applied the first article of this series - SQL Server 2008 New Features (Row Constructors).

Step-by-Step Explanation

Follow the easy steps below to create your environment and work area.

1.    Open Microsoft SQL Server Management Studio.

2.    Right click the database DbWork, choose the query window and Enter the listing below to create a table for the organization departments.

Listing 1: T-SQL to create a table for holding the organization departments

CREATE TABLE [dbo].[TblOrgRep](
[DeptHID] [hierarchyid] Primary Key,
[DeptId] [intNULL,
[DeptName] [varchar](50) NULL)

3.    Please note that the first field of the table was of type hierarchyid, the new data type in SQL Server 2008.

4.    SQL Server has provided functions to deal with this new type of variables. Namely, there are four of them of interest to us (ToString, GetRoot(), GetDescendant(), GetAncestor()). Please respect the case when typing the functions name.

5.    The ToString() function returns a string representation of the hierarchy data type in the format of slash succession. You will see that in the demo picture.

6.    The GetRoot() function returns the node value in a specific value (represented by a slash).

7.    The GetDescendant() function returns a child node ID for a specific father depending whether that ancestor has a child or not (take two child ID's as parameters to insert between them).

8.    The GetAncestor() function returns the parent of a given child. Please note it also takes one parameter as int to decide how many level it should go up in the hierarchy.

9.    To illustrate the ideas using an example, I need to show a picture with a simple organization chart. Find the image below.

10. As you can, when I will call the GetRoot() method on this chart, I will get Sami as head.

11. When I call the GetDescendant() method on Sami, I will get Kamal, Omar, Rawad as well as Tammam.

12. When I call the GetAncestor(1) method on Taher, I will get Kamal. Also, GetAncestor(2) on Yehia will get me Sami.

Figure 1 - The Organization Chart of Departments to develop

13. After explaining the methods, we move to the building part of the hierarchy.

14. We first need to insert the root (first record) of the organization chart.

15. Listing 2 provides the insert part. The GetRoot() is called to create the root ID in the table.

Listing 2 - T-SQL Showing insert of the root node

/* Insert Into the root node */
 Insert Into TblOrgRep (DeptHID, DeptId, DeptName, DeptHeadName)
 Values (hierarchyid::GetRoot(), 1, 'IT Dept','Sami')
/* Select Data to insure that data is inserted */
Select DeptHid.ToString(), * From TblOrgRep

16.  Afterwards, we need to create the whole organization chart. In order to do this I am going to create a stored procedure to create consequent records using one exec statement. Listing 3 shows the created stored procedure.

Listing 3 - The stored procedure to insert the records into the TblOrgRep

Create Procedure InsertDept(@OrgId as Int, @DeptId as int, @DeptName varchar(50), 
@DeptHeadName varchar(50)) As
      Declare @OrgHId as hierarchyId
      Declare @LastSupervisedOrg as hierarchyId
      Select @OrgHId = DeptHID From TblOrgRep Where DeptId = @OrgId
      Select @LastSupervisedOrg = Max(DeptHID) From TblOrgRep 
        Where DeptHId.GetAncestor(1) = @OrgHId
      Insert Into TblOrgRep Values 
      return @@RowCount

17. The stored procedure accepts as parameter the department ID to insert below it as @OrgId.

18. The rest of the parameters are simply the data to be entered into the table.

19. The stored procedure starts by retrieving the hierarchy ID of the supplied organization ID.

20. The second step is to get the last organization hierarchy ID that is a descendant of the supplied organization ID. If no descendant is found, then the @LastSupervisedOrg would be null. And the Insert line would create a new child, else it will create a next child.

21. Note that you can always test by selecting data like in Listing 2 after each exec to from the stored procedure.

22. Listing 4 shows the SQL statements used to create the remaining of the organization chart.

Listing 4

Exec InsertDept 1,2,'Development','Kamal'
Exec InsertDept 2,6,'Windows/Web','Taher'
Exec InsertDept 2,7,'AS400','Yehia'
Exec InsertDept 1,3,'Network','Omar'
Exec InsertDept 3,8,'Support','Bashir'
Exec InsertDept 3,9,'Routers','Charif'
Exec InsertDept 1,4,'Operations','Rawad'
Exec InsertDept 1,5,'Portals','Tammam'
/* Select Data to insure that data is inserted */
Select DeptHid.ToString(), * From TblOrgRep

23. The listing above should result in the following picture that shows the organization chart as selected from the table.

Figure 2 - Shows the full selection after insertion of data

24. Some additional listings are provided below in order to illustrate the idea of selecting data from the table. Please try them.

Listing 4 - T-SQL to select the name of head of organization

Select DeptHid.ToString(), * From TblOrgRep Where DeptHid = hierarchyid::GetRoot()

Listing 5

Declare @OrgHid as HierarchyId
Select @OrgHId = DeptHID from TblOrgRep Where DeptId = 6
Select @OrgHid.GetAncestor(2).ToString(), * From TblOrgRep 
  Where DeptHid = @OrgHid.GetAncestor(2)


I did leave some queries for you to try, such as select all descendants of a given ID (should be pretty easy…hint, do not use the max function in the stored procedure) as well as select all dept at the third level. You can also investigate the GetLevel() method not explained here.

The Hierarchy ID is a new data type that I do expect to have future use in Visio or in stand alone tools.

©Copyright 1998-2019  |  Page Processed at 2019-11-17 11:02:31 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search