USEFUL SITES :
Write for Us
Parent
Child1
Child2
Return
NULL
Not NULL
Child of parent
Child of parent greater than Child1
Child of parent less than Child2
Child of parent greater than child1 and less than child2.
Child1 or Child2 is not NULL but is not a child of parent
Exception
Child1 >= Child2
DECLARE @Manager hierarchyid SELECT @Manager = hierarchyid::GetRoot() FROM dbo.Employee INSERT INTO [dbo].[Employee] ([Name] ,[Designation] ,[HierarchyID]) VALUES ('Andy' ,'Chief Marketing Officer' ,@Manager.GetDescendant(NULL,NULL)) The above code will insert Andy into the table with HierarchyID of /1/ . DECLARE @Manager hierarchyid DECLARE @FirstChild hierarchyid SELECT @Manager = hierarchyid::GetRoot() FROM dbo.Employee SELECT @FirstChild =@Manager.GetDescendant(NULL,NULL)
INSERT INTO [dbo].[Employee] ([Name] ,[Designation] ,[HierarchyID]) VALUES ('Lessa' ,'Finance Controller' ,@Manager.GetDescendant(@FirstChild,NULL)) The above code will insert Lessa into the table with a HierarchyID of /2/. @Manager.GetDescendant(@FirstChild,NULL) will return /2/ as @FirstChild is /1/ DECLARE @Manager hierarchyid DECLARE @FirstChild hierarchyid DECLARE @SecondChild hierarchyid SELECT @Manager = hierarchyid::GetRoot() FROM dbo.Employee SELECT @FirstChild =@Manager.GetDescendant(NULL,NULL) SELECT @SecondChild =@Manager.GetDescendant(@FirstChild,NULL) INSERT INTO [dbo].[Employee] ([Name] ,[Designation] ,[HierarchyID]) VALUES ('Tony' ,'CIO' ,@Manager.GetDescendant(@SecondChild,NULL)) After inserting the data, your employee table will look like the following:
This illustarted how the HierarchyID column data is inserted. Using Stored Procedure Even though the previous examples show how to insert data, in the real world example we would add employee using a stored procedure by passing the relevant parameters. The parameters will be Employee Name, the designation and the Manager’s employee ID. The Following stored procedure will add an employee. CREATE PROCEDURE [dbo].[InsertEmployee] (@ManagerID int, @EmpName varchar(50), @Designation varchar(100) ) AS BEGIN DECLARE @varlastChild VARCHAR(10) ,@hieManagrID HIERARCHYID ,@LastChild HIERARCHYID ---- Get the hierarchyid of the manager SELECT @hieManagrID = HIERARCHYID FROM Employee WHERE ID = @ManagerID Set @varlastChild = @hieManagrID.ToString() ---- Get the MAX hierarchyid of the next level where the employee should be in SELECT @LastChild = MAX(HIERARCHYID) FROM Employee WHERE hierarchyid.ToString() LIKE @varLastChild +'[0-9]/' INSERT INTO Employee (Name,Designation,hierarchyid) VALUES (@EmpName,@Designation,@hieManagrID.GetDescendant(@LastChild,NULL)) END ;
The new method ToString() is used in the above script. This method is useful to get the string representation of the HierarchyID. The method returns a string that is a nvarchar(4000) data type. The syntax of this method is node.ToString ( ). The Parse() method is the method used to convert string into HierarchyID which is the opposite of the ToString() method. If you run the following script, you will have a record with hierachyid of /3/1/3/. EXEC [dbo].[InsertEmployee]
@ManagerID = 9, @EmpName = 'Sandrina', @Designation = 'QA ' Data Retrieval The next step is to retrieve your data. Let's see how we can get the results we got from the SQL Server 2005 by using CTE. SELECT ID ,Name ,Designation , Hierarchyid.GetLevel() as Level ,(Select ID FRom Employee Where Hierarchyid = e.Hierarchyid.GetAncestor(1)) As ManagerID FROM employee e In the above script we are using two new methods named, GetLevel and GetAncestor. The GetLevel() method is useful to find the Level of the current node. This method will return an integer that represents the depth of this node in the current tree. The GetAncestor() method will give you the hierarchyID of the parent node. However, in the case of inserting a new level to a table with a HierarchyID column in not easy. You have to change all the parents using a Reparent() method. This is a very useful method which helps you to reparent a node i.e. suppose if we want to align an existing node to a new parent or any other existing parent then this method is very useful. Syntax: node.Reparent ( oldRoot, newRoot ) Use of HierarchyID Apart from saving organizational structure, there are several other structures that you can save with using hierarchyid in SQL Server 2008. A file system which has directory hierarchy A set of tasks in a project in which one task may be divided into several sub tasks. Goods which consists of other goods and raw materials. Issues of HierarchyID Data Type There is no straight forward way of finding the next available Hierarchy ID. You need to find the maximum current hierarchy and then get the next available hierarchy. Reparent is easy and you have to reassign all the relevant records. You cannot have two roots in one table. Root will be displayed as /, therefore you cannot have two roots. However there are two ways to tackle this issue. You can have an additional column. In the case of our previous example, you can have another column saying OrganizationID. So OrganizationID and HierarchyID make a unique key. Another way of doing this is having one root and the next level should be assigned to the organization. However, it would be better if we could assign the root with some value, so that we can have any number of roots in the table.
Issues of HierarchyID Data Type
However, it would be better if we could assign the root with some value, so that we can have any number of roots in the table.
