I've recently been deliverying training sessions on SQL
Server 2008 and doing launch events. The training covers all aspects of SQL
Server development.
For most features I can provide a strong argument for the feature with the
exception of one. HierarchyId.
HierarchyId is the new data type that allows you to store a hierarchy in
a single column. This is using a path enumeration model, just like the path to a
file on the file system.
The benefit of HierarchyId is that the storage structure is very effiecient
it uses a thing called Ord Path.
With this efficient data type querying for the children of a parent performs
very well.
However the one thing that just baffles me is the amendment of hierarchies
and moving of nodes in a hierarchy. It baffles me because this I
foresee to be one of the major requirements for a hierarchical data, yet the
data type doesn't really support it.
Ok so you will see a method Reparent(). However this doesn't do
anything to cater for existing data in your hierarchy. Essentially all the
methods for the data type know about only the instance of the type (i.e.
column value or variable) that the function i being used with. It doesn't do
anything to check the other rows in the table.
You will find that if you want to add a node to a parent. You have to find
the last child of that parent and then add the new node to the parent but after
this last child. This isn't difficult but cubersome code that you will have to
repeat again and again. A bit like the standard code you need for a Service
Broker activation procedure.
Whats more none of the functions are set based and so if you want to add two
children you have to add one at a time because to add the second you need to add
it after the first.
So all in all I'm a bit disappointed by the HierarchyId type I do hope they
make it more functional in future releases.
If youre interested in set based Hierarchy Id solutions I have a few but they
aren;t pretty. I will blog about them over the next few days.