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.

Do WCF Data Contracts have relevance outside of WCF

In working with WCF you will come across DataContract attributes which decorate DTOs. DataContracts are great because they allow you to specify the WCF contract and are much more flexible than the XML serializer used by the original web services in .NET in that you can specify the attributes on prvate fields so you don't need to break encapsulation. It also supports versioning, optional parameters and a much fuller XSD model

However, can we take this feature which appears to have been created for WCF and use it more generically?

My view and way that I use this is for any sort of data contracts between layers of an application, so between the UI and the business tier, between services, between service layers and even into serialized persistent stores (e.g. XML datatype in the database).

Why?

• It is flexible in terms of versioning, optionality and data encapsulation
• It is faster than Binary or XML Serialization (in my testing)
• It creates compact, concise output

It seems Microsoft had this in mind when they created the class as it is placed in the System.Runtime.Serialization namespace.

So I would recommend that you use data contracts when passing data between any layer of an application and enforce that contract to ensure compatibility.

This relates to a number of upcoming articles, so stick with me while I evolve the story around DataContracts and how you might use them across the application from the data tier, to the UI and avoid writing lots of mappers between domain objects, DTOs and persistent entities.

Feel free to give your views on this, either positive or negative in the comments below