I gave a presentation Tuesday to my local SQL user group on 2008 T-SQL and data types. One of the items covered was HierarchyID. I had a simple but thorough code sample that demonstrated most of the functions. I guess I have been out of the loop lately, because at some point in the presentation, fellow MVP Dan Guzman mentioned that RC0 had been available for a week.  I was using the Feb CTP thinking that was the latest available.

Next week I am giving the same presentation to the VB group in town and will make sure I am using RC0. I decided that I should take a look at the release notes before getting it installed. I found out that two of the functions used with HiearchyID have changed names. Instead of Reparent(), now you must use GetReparentedValue(). Instead of IsDescendant() you must use IsDecendantOf().  The first change is just a matter of changing the function name. In the second case, the function is used in a converse manner.

HierarchyID is used to represent a node in a hierarchy such as a family tree, an org chart or a bill of materials. It is more difficult to insert and update the rows but much easier and more efficient to query them. This is a CLR-based data type with methods and properties much like a class. When adding a new row, you have to get the node of the parent and figure out the last child added before the row can be inserted. It took me a bit of time to figure out what was going on.

This new data type is pretty interesting, but the group decided that it might not be worth using. For example, I found when playing around with it that it let me delete an intermediate level node. When you move a node the children of that node get left behind. You would have to make sure that you account for those issues in stored procedures or the application logic.