The hierarchyid Data Type

Last night’s North Dallas .Net User Group meeting was really good. Peter Debetta presented new features on SQL 2008. All of it was really interesting and will eventually be useful. I wouldn’t want to rehash all of the Microsoft documents and other blog information, but I will say that there are some useful features on the way.

The one thing that I appreciated but felt a little pang of irritation was the new “hierarchyId” data type. I spent many a night trying to get this perfect in a past project as did Peter. This data type holds the relationship of the contained data in relation to its parent with easy access and update to Ancestors and Descendents. The one downside is that this new data type only manages trees of data and not a recursive matrix.

I’ll jump on this feature next time I have to utilize an optimized tree of data using stored procedures. The other memorable feature is the Merge function that makes synchronizing records more natural.  It's a bit limiting but it works.

So SQL 2008 is scheduled for release third quarter, but we know that companies are slow to adopt, even with all of the great features and the huge beta testers out there. At least sometimes we can start projects with the express versions and use these features to prove the product before production databases get converted.

We can read more about the hierarchyid data type at:
http://msdn.microsoft.com/en-us/library/bb677290(SQL.100).aspx

-Vince