Microsoft® SQL Server® 2012 Bible

325

CHAPTER

13

Working with Hierarchies

IN THIS CHAPTER

Using HierarchyID

Understanding Hierarchical Data Alternatives

E

ver since there have been databases, there has been a need to fi t 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 hierar-

chical 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 defi ned 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 fl avor of hierarchical data when tracking managers

and employees. SQL Server 2005 implemented the Common Table Expression (CTE), which included

the recursive query, which enabled traversing hierarchical data much easier. But you still needed to

implement the solution yourself.

The great thing is that wonderful enhancements have been made in the area of supporting

hierarchical data in a relational database. SQL Server 2008 introduced the Hierarchy data type,

which helps many of the problems of storing and querying hierarchical information. However, it

is still hierarchical data; data that doesn’t easily lend itself to relational format in a relational

database.

There are three main approaches and techniques for working with hierarchical data:

■ HierarchyID

■ (^) Adjacency List
■ Materialized Path
c13.indd 325c13.indd 325 7/30/2012 4:47:03 PM7/30/2012 4:47:03 PM
http://www.it-ebooks.info