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:

image 

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'

image

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.