Hierarchy made easier

Well, we are on a roll now, three days in a row! Today let's examine some new data types that have been added to SQL Server 2008.

SQL Server 2008 offers several new data types. One of the more interesting is the HierarchyID type. The HierarchyID data type is implemented as a CLR UDT (User defined type) and is exposed to CLR clients as SqlHierarchyId. The HierarchyID type will allow you to model organization structures, tree structures and hierarchies.
This new data type comes with many built-in methods that will allow you to manipulate and edit hierarchical data.
The methods exposed by the HierarchyID are:

child.GetAncestor(n) - this returns the HierarchyID of the nth ancestor

parent.GetDescendant(child1, child2) - this returns a child node

node.GetLevel() - returns an integer value of the depth of the node in the structure

hierarchyid::GetRoot() - returns the root of the structure

parent.IsDescendant(child) - determines if a node is a descendant of another node

hierarchyid::Parse(string) - converts a string representation of a hierarchyid into a hierarchyid value

void Read(BinaryReader x) - for CLR clients, reads a binary representation of a SqlHierarchyId object from the BinaryReader parameter and sets the SqlHierarchyId object to that value

node.Reparent(OldRoot, NewRoot) - move a node in the hierarchy from one parent to another

node.ToString() - returns a string with a logical representation of the node

void Write(BinaryWriter x) - for CLR clients, writes a binary representation of a SqlHierarchyId object to the BinaryWriter parameter.

You can use any of the above methods that return a node (representation of the hierarchy) in place of HierarchyID data type.

Let's look at how the HierarchyID works (as seen on Ravi Maniam's blog (http://blogs.msdn.com/manisblog/archive/2007/08/17/sql-server-2008-hierarchyid.aspx);

Use AdventureWorks
Go  
--Create Scheme
Create Schema HumanResources 
Go  
--Create Table  
CREATE TABLE HumanResources.EmployeeDemo 
( 
OrgNode HIERARCHYID, 
EmployeeID INT, 
LoginID VARCHAR(100), 
Title VARCHAR(200), 
HireDate DATETIME 
) 
Go 
--Create Index
CREATE UNIQUE CLUSTERED INDEX idxEmployeeDemo 
ON HumanResources.EmployeeDemo (OrgNode,EmployeeID) 

--Insert First Row       
INSERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)              
VALUES (hierarchyid.GetRoot(), 1,'adventure-works\scott', 'CEO', '3/11/05') ; 
Go           
--Insert Second Row      
DECLARE @Manager hierarchyid            
SELECT @Manager = hierarchyid.GetRoot() FROM HumanResources.EmployeeDemo; 
INSERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)              
VALUES (@Manager.GetDescendant(NULL,NULL), 2, 'adventure-works\Mark',            
'CTO', '4/05/07')         
Go 
--Insert Third Row
DECLARE @Manager hierarchyid            
DECLARE @FirstChild hierarchyid         
SELECT @Manager = hierarchyid.GetRoot() FROM HumanResources.EmployeeDemo; 
Select @FirstChild = @Manager.GetDescendant(NULL,NULL)              
INSERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)              
VALUES (@Manager.GetDescendant(@FirstChild,NULL), 3, 'adventure-works\ravi',            
'Director Marketing', '4/08/07')        
Go           
--Insert the First Descendant of a Child Node         
DECLARE @Manager hierarchyid            
SELECT @Manager = CAST('/1/' AS hierarchyid)          
INSERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)              
VALUES (@Manager.GetDescendant(NULL, NULL),45,        
'adventure-works\Ben','Application Developer', '6/11/07') ;         
Go           
--Insert the Second Descendant of a Child Node        
DECLARE @Manager hierarchyid            
DECLARE @FirstChild hierarchyid         
SELECT @Manager = CAST('/1/' AS hierarchyid)          
SELECT @FirstChild = @Manager.GetDescendant(NULL,NULL)              
INSERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)              
VALUES (@Manager.GetDescendant(@FirstChild, NULL),55,        
'adventure-works\Laura','Trainee Developer', '6/11/07') ;           
Go           
--Insert the first node who is the Descendant of Director Marketing       
DECLARE @Manager hierarchyid            
DECLARE @FirstChild hierarchyid         
SELECT @Manager = CAST('/2/' AS hierarchyid)          
INSERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)              
VALUES (@Manager.GetDescendant(NULL, NULL),551,              
'adventure-works\frank','Trainee Sales Exec.', '12/11/07') ;        
Go           
--Insert the second node who is the Descendant of Director
--Marketing             
DECLARE @Manager hierarchyid            
DECLARE @FirstChild hierarchyid         
SELECT @Manager = CAST('/2/' AS hierarchyid)          
SELECT @FirstChild = @Manager.GetDescendant(NULL,NULL)              
INSERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)              
VALUES (@Manager.GetDescendant(@FirstChild, NULL),531,              
'adventure-works\vijay','Manager Industrial Sales', '12/09/06') ;         
Go           
--Insert the third node who is the Descendant of Director 
-- Marketing              
--in between 2 existing descendants            
DECLARE @Manager hierarchyid            
DECLARE @FirstChild hierarchyid         
DECLARE @SecondChild hierarchyid        
SELECT @Manager = CAST('/2/' AS hierarchyid)          
SELECT @FirstChild = @Manager.GetDescendant(NULL,NULL)              
SELECT @SecondChild = @Manager.GetDescendant(@FirstChild,NULL)            
INSERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate)              
VALUES (@Manager.GetDescendant(@FirstChild, @SecondChild),543,            
'adventure-works\james','Manager Consumer Sales', '12/04/06') ;
--Use Serializable Transaction 
CREATE PROCEDURE AddEmployee(@ManagerID hierarchyid, @EmpID int, 
@LogID varchar(100), @JobTitle as varchar(200), @JoiningDate datetime) 
AS 
BEGIN 
DECLARE @LastChild hierarchyid 
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 
BEGIN TRANSACTION 
SELECT @LastChild = Max(OrgNode) From HumanResources.EmployeeDemo 
WHERE OrgNode = @ManagerID 
INSERT HumanResources.EmployeeDemo (OrgNode, EmployeeID, LoginID, Title, HireDate) 
VALUES(@LastChild, @EmpID,@LogID , @JobTitle, @JoiningDate) 
COMMIT 
END ;

 

Please post any questions in the comments and I'll get back to you quickly. I hope you can find a use for the HierarchyID data type in your day to day work.

Have a good weekend and we'll be back on Monday. Remember to post any suggestions for topics or specific questions. You can also email me at sql.slinger@gmail.com.

Technorati Tags: SQL Server,SQL Server 2008,T-SQL,Data Types,Katmai,Microsoft