Hierarchyid column type in SQL 2008

If you have ever tried to create an org chart, document structure or other type of hierarchy in SQL you will be aware of the self-join table pattern which works at a basic level but is hard to have arbitrary flexibility in the number of levels and selecting everything below a particular node.

It is interesting to note that Microsoft added a new column type in SQL 2008 which allows you to represent this relationship natively in the database. I was aware of it but hadn't had much use for it or delved too deeply until I read this article:

http://blogs.msdn.com/b/simonince/archive/2008/10/17/hierarchies-with-hierarchyid-in-sql-2008.aspx

This is a very powerful feature in particular the T-SQL extensions for placing the row in the hierarchy (such as hierarchyid::GetRoot()) and also the ability to select all nodes below a particular sub-tree, so finding all reports of a particular manager (direct and in-direct) is easy.

SELECT

    Id,

    Id.ToString() AS [Path],

    Id.GetLevel() AS [Level],

    Name

FROM EmployeeWithHierarchyID

WHERE Id.IsDescendantOf('/5/') = 1

This does not replace the need for a hierarchical database, but it does make SQL server more versatile and meets a key need for document repositories. I wonder if and when SharePoint will use this for a range of features such as document repositories.