Microsoft SQL Server 2012 Bible

Summary

The hierarchyid has been around for a few years now, and it really hasn't changed much since it was first introduced. Its purpose is simply to represent positions within a hierarchy, but the key to understanding the hierarchyid data type is understanding that it does not automatically represent a hierarchy tree. Instead, it is up to the application to generate and assign the appropriate hierarchy values. The trick is to do this is a way that reflects the relationships between the rows accurately.

This chapter covered the data type methods that provide efficient querying of the hierarchyid data type. However, you also looked briefly at a well-known alternative to the hierarchyid data type, XML. XML has been around for a lot longer than the hierarchyid and has roots deep into describing and shaping data, such as hierarchies.

Unfortunately, the two don't quite mesh yet, meaning that you can't query the hierarchyid and simply return a nicely shaped XML document. Regardless, the hierarchyid is an excellent method for storing hierarchical data, and with the supported methods makes is much more efficient and a performant solution over XML.