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 ..
declare @ParentNode hierarchyid,
— get the node for the parent of the new
select @ParentNode = DepartmentHierarchyNode
where DepartmentID = @ParentID
— get the last department for the parent
select @MaxChildNode = max(DepartmentHierarchyNode)
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)
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
declare @TopNode hierarchyid,
select @TopNode = DepartmentHierarchyNode,
@TopLevel = DepartmentHierarchyLevel
where DepartmentName = @DepartmentName
— note that parent is descendent of itself
select DepartmentHierarchyNode.ToString() NodeText, space((DepartmentHierarchyLevel – @TopLevel) * 5) + DepartmentName Department
where DepartmentHierarchyNode.IsDescendantOf(@TopNode) = 1
order by DepartmentHierarchyNode
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 the moved node as well, you must do this yourself.