Microsoft® SQL Server® 2008 Bible

Chapter 17. Traversing Hierarchies

IN THIS CHAPTER

• Hierarchical patterns

• Hierarchical user-defined functions (UDFs)

• Recursive CTE queries

• Materialized lists

• HierarchyID data type

Traditionally, SQL has had a hard time getting along with data that doesn't fit well into a relational grid, and that includes hierarchical data. The lack of an elegant solution became obvious when working with family trees, bills of materials, organizational charts, layers of jurisdictions, or modeling O-O class inheritance. At best, the older methods of handling hierarchies were more of a clumsy work-around than a solution.

The problems surrounding hierarchical data involve modeling the data, navigating the tree, selecting multiple generations of ancestors or descendents, or manipulating the tree—i.e., moving portions of the tree to another location or inserting items. When the requirements demand a many-to-many relationship, such as a bill of materials, the relationships become even more complex.

New query methods, new data types, and a better understanding of hierarchical information by the SQL community have coalesced to make this an area where SQL Server offers intelligent, scalable solutions to hierarchical problems.

Is managing hierarchical data as easy as SELECT * FROM too? No. Hierarchies still don't fit the traditional relational model so it takes some work to understand and code a database that includes a hierarchy.

The initial question when working with hierarchical data is how to store the hierarchy, ...