SQL Server 2008 New Features - Hierarchy Data Type
page 1 of 1
Published: 12 Sep 2008
The hierarchy data in SQL Server 2008 is best used to implement organization chart as well as tree based kind of variables. In this article, Nidal demonstrates this data type in a step-by-step way. He examines each step in detail with the help of relevant SQL statements and screenshots of the final result.
by Nidal Arabi
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 22809/ 36


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.

User Comments

Title: Herarchy Id Data Type   
Name: shailendra mishra
Date: 2012-07-10 5:14:59 AM
I am not able to see picture which you added into you site so this article is of no use for me.....
Title: IT Manager   
Name: .DN.
Date: 2011-03-07 11:03:21 AM
Precise and consice,
Nidal Arabi you have gift explaining

Thank You.
Title: herarchyid   
Name: swati gupta
Date: 2011-01-18 4:10:43 AM
the above infprmation is very good tio make one understand this new concept....
Gooooooooooddddddddd job
Title: Ms   
Name: PS
Date: 2009-12-23 4:17:43 AM
simple and precise article. very helpful especially for beginners.
Title: Developer   
Name: Azees
Date: 2008-11-29 1:41:16 AM
Excellent article its help full to me
Title: Senior Developer   
Name: Hung Tran Dinh
Date: 2008-09-15 12:52:29 AM
It's not interestng to me, i prefer CONNECT BY PRIOR statement in oracle.
Title: Error while inserting..   
Name: jazz
Date: 2008-09-14 5:19:22 PM
I was doing this example..when i created the table it has 3 columns..DeptHID,DeptId,DeptName and while inserting it is taking 4 columns..just want to know did you miss in the example DeptHeadName as column while creating the table.

Community Advice: ASP | SQL | XML | Regular Expressions | Windows

©Copyright 1998-2017 ASPAlliance.com  |  Page Processed at 2017-03-30 10:39:07 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search