SQL Server™ 2005 Bible

Chapter 12. Navigating Hierarchical Data

In This Chapter

• Storing hierarchical data

• Hierarchical user-defined functions (UDFs)

• Recursive CTE queries

No tool is best for every job and as much as I believe SQL is the romance language of data, SQL has a hard time getting along with certain types of data. To put is bluntly, SQL is just plain clumsy at retrieving multiple levels of genealogies or hierarchical data. The lack of an elegant solution becomes obvious when working with family trees, bills of materials, organizational charts, or layers of jurisdictions, or modeling O-O class inheritance.

The problems surrounding hierarchical data involve modeling the data, navigating the tree, selecting multiple generations of ancestors or descendents, or moving portions of the tree to another location. When the requirements demand a many-to-many relationship, such as when many parts may be used within many bills of materials to build multiple other parts, the relationships become even more complex.

This chapter walks you through the various methods of storing and then navigating hierarchical data—from the single-generation reflective join to the cumbersome cursor, and on to the efficient user-defined function.

Note

The ANSI SQL 99 standard (a link to which is provided at this book's website, www.SQLServerBible.com) attempts to deal with hierarchical data (adjacency list pattern) with the introduction of the common table expression, or CTE, which can be used to select multiple levels of data from a ...