Microsoft SQL Server 2012 Bible

Chapter 13

Working with Hierarchies

In This Chapter

Using HierarchyID

Understanding Hierarchical Data Alternatives

Ever since there have been databases, there has been a need to fit nonrelational data in the databases. Anyone can quickly put together a list of this type of data, but one of the most often used and the subject of this chapter is hierarchical data. Included in this type of hierarchical data are organizational charts, genealogical data, manufacturing data (Bill of Material), and even Object-Oriented class inheritance. This list could go on as well, but the problem is that there hasn't been a decent solution for solving hierarchical data within the walls of a relational database.

Hierarchical data is defined as data items related to each other by their hierarchical relationship. OK, easy enough; but if you look at the underlying structure of any type of hierarchical data, the problems that surface when working with this type of data are evident. How do you traverse a hierarchy tree? How do you easily look at ancestor or descendant data? How do you manipulate the hierarchical tree?

The basis for hierarchical data is its storage. What is the proper structure and format for storing hierarchical data? Many things have been tried over the years, and even the sample AdventureWorks database implements its own flavor of hierarchical data when tracking managers and employees. SQL Server 2005 implemented the Common Table Expression (CTE), which included the recursive query, ...