SQL Server 2008 - Hierarchy ID Data Type

To show this we need to create a table with the new data type..

create table [dbo].Organisation (
DepartmentID int primary key nonclustered,
DepartmentName varchar(100) not null,
DepartmentHierarchyNode hierarchyid not null,
DepartmentHierarchyLevel as DepartmentHierarchyNode.GetLevel() persisted
-- plus any other columns you need
)

Note the level is a derived function of getLevel() which returns how deep a node is in the structure in this case it’s the only node and so will return 0.

Now we need to put the some data in starting at the top of the tree i.e the whole company – which in this example is Contoso..

insert Organisation(DepartmentID, DepartmentHierarchyNode, DepartmentName)
values (1, hierarchyid::GetRoot(), 'Contoso')

note the getroot() function which does what is says and gets the root node of the hierarchy and since this is the first node in the hierarchy this is simply itself.

Adding in new rows to this table is best done with a stored procedure like this one which needs to know the id for the new department the name of it and the id of the parent department it belongs to ..

create

procedure AddDepartment

@DepartmentID

int,

@DepartmentName

varchar(100),

@ParentID

int

as

begin

declare @ParentNode hierarchyid,

@MaxChildNode

hierarchyid

begin transaction

-- get the node for the parent of the new

-- department

select @ParentNode = DepartmentHierarchyNode

from Organisation

where DepartmentID = @ParentID

-- get the last department for the parent

select @MaxChildNode = max(DepartmentHierarchyNode)

from Organisation

where @ParentNode = DepartmentHierarchyNode.GetAncestor(1)

-- add the new department

-- Parent.GetDescendant(MaxChild, null) returns the

-- next descendent

insert Organisation (DepartmentID, DepartmentHierarchyNode, DepartmentName)

values (@DepartmentID, @ParentNode.GetDescendant(@MaxChildNode, null), @DepartmentName)

commit

end

Here you can se more obviously named functions of a HeirarchyID like GetAncestor, GetDescendant and MaxChild.  I have to admit I have no problem with these as they are quite similar to what you use in MDX in Analysis Services.

Here’s some data to go into the new table..

exec AddDepartment 2, 'Operations', 1
exec AddDepartment 3, 'Development', 1
exec AddDepartment 4, 'Parking', 1
exec AddDepartment 5, 'Home Operations', 2
exec AddDepartment 6, 'Field Operations', 2
exec AddDepartment 7, 'North Territory', 6
exec AddDepartment 8, 'South Territory', 6
exec AddDepartment 9, 'Database', 3
exec AddDepartment 10, 'Services', 3
exec AddDepartment 11, 'Applications', 3
exec AddDepartment 12, 'Windows', 11
exec AddDepartment 13, 'Internet', 11
exec AddDepartment 14, 'Self', 4
exec AddDepartment 15, 'Valet', 4

selecting from this will give you the levels and so on:

 

This procedure will show a tree of the structure using a single select statement

create procedure ShowDepartmentChart
@DepartmentName varchar(50)
as
begin

  declare @TopNode hierarchyid,
@TopLevel int

  select @TopNode = DepartmentHierarchyNode,
@TopLevel = DepartmentHierarchyLevel
from Organisation
where DepartmentName = @DepartmentName

  -- note that parent is descendent of itself
select DepartmentHierarchyNode.ToString() NodeText, space((DepartmentHierarchyLevel - @TopLevel) * 5) + DepartmentName Department
from Organisation
where DepartmentHierarchyNode.IsDescendantOf(@TopNode) = 1
order by DepartmentHierarchyNode

end

go

 

and then use this like this..

exec ShowDepartmentChart 'Contoso'

The common toSting() function returns the human readable form of the hierarchyID node.

You can (and should!) index Hierarchy ID’s in two ways..

1. Create a "breadth first" index,  to speed up selecting of nodes at the same level, all the sub departments that belong to a parent department

create clustered index Organisation_Breadth_First
on [dbo].Organisation( DepartmentHierarchyLevel, DepartmentHierarchyNode )

2. Create a "depth first" index, to speed up selecting a sub-tree
of nodes, e.g. all the departments beneath a particular parent department

create unique index Orgisation_Depth_First
on [dbo].Organisation(DepartmentHierarchyNode )

Hierarchy ID’s are faster than self joins, not least because no recursive SQL is needed and in my opinion more intuitive.  However one important thing to remember is that don’t enforce any kind of integrity so like with self joins you can end up with orphans. One area like this where you can stuff things up is if you use the reparent function. this moves a node in the structure to a differnet parent ( for example if a mmeber of staff has a new manager), but it doesn't move the nodes underneath themovednodeaswell, you must do this yourself.

Comments

• Anonymous
  December 11, 2008
  your explanation is the best i've found on the net

• Anonymous
  February 04, 2009
  Not able to create the table. "create table [dbo].Organisation ..." is throwing the error as 'Incorrect syntax near 'hierarchyid ''

• Anonymous
  February 05, 2009
  Gajanana Cut copy and paste form this article should work - it's waht I did! Andrew

• Anonymous
  July 08, 2009
  The comment has been removed

• Anonymous
  July 09, 2009
  Burt Apologies fixed

• Anonymous
  July 12, 2009
  Few notes: OrgNew function in the Sproc does not exist. Did you meant to say the table name Organisation here or this code is incomplete? Column "Node" does not exist in your table (see your sproc). Again did you meant to say DepartmentHierarchyNode here?

• Anonymous
  July 14, 2009
  Dan, all clealry something weird went on when I published this so I am going to rewrtie the code and put it back in a day or so, please accept my apologies in the meantime

• Anonymous
  July 14, 2009
  All, Code has been fixed and tested so should be all good now

• Anonymous
  July 21, 2009
  Thank you very much! I completely agree with nick. Your explanation and solution for searching the tree structure is the best I found on the internet. Thanks again.

• Anonymous
  December 07, 2010
  Amazing explination i totally agree with them thanks