Insufficient data from Andrew Fryer

The place where I page to when my brain is full up of stuff about the Microsoft platform

SQL Server 2008 - Hierarchy ID Data Type

SQL Server 2008 - Hierarchy ID Data Type

  • Comments 10
  • Likes

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 the moved node as well, you must do this yourself. 

Comments
  • your explanation is the best i've found on the net

  • Not able to create the table.

    "create table [dbo].Organisation ..." is throwing the error as 'Incorrect syntax near 'hierarchyid ''

  • Gajanana

    Cut copy and paste form this article should work - it's waht I did!

    Andrew

  • Re:'Incorrect syntax near 'hierarchyid '

    Get rid of 'new' in the 3rd line

    ( DepartmentName varchar(100) not null,  new )

  • Burt

    Apologies fixed

  • 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?

  • 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

  • All, Code has been fixed and tested so should be all good now

  • 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.

  • Amazing explination i totally agree with them

    thanks

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment