SQL Server 2008 - HierarchyId whats the point?

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.



Published Saturday, March 29, 2008 11:40 PM by simonsabin
Filed under: ,


Monday, March 31, 2008 3:43 PM by SimonS Blog on SQL Server Stuff

# SQL Server 2008 - HierarchyId - How do you move nodes/subtrees around

In a recent post SQL Server 2008 - HierarchyId whats the point? I talk about the scenarios that the new