Introduction
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.
Requirements
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] [int] NULL,
[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
Begin
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
(@OrgHid.GetDescendant(@LastSupervisedOrg,Null),@DeptId,@DeptName,@DeptHeadName)
return @@RowCount
End
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)
Summary
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.
