GPO (11/13/2012)
...whole books, hundreds of chapters, and thousands of Internet articles have been written on the subject and it's impossible for me to cover all of the aspects of the subject in a single article...
...I have to assume that you already know what the Adjacency List and Nested Sets structures are and how they are used...
Can anyone recommend a primer article or two on Adjacency List and Nested Sets structures?
I don't normally reommend books but the most complete book on all 3 types of hierarchies (Adjacency List, Materialized Hierarchical Path, and Nested Sets) was written by Joe Celko. It's titled "Trees and Hierarchies in SQL for Smarties". His second edition of the book is out and available in places like Amazon.com.
I word of warning. Mr. Celko didn't write the book specifically for SQL Server. He writes in mostly ANSI Standard code. Although his examples are easy to understand, you will have to redact some of his code to make it work in SQL Server. The book is a pretty easy read so that shouldn't be a problem for most folks.
I'll also say that, in the first edition anyway, he doesn't have code to create huge hierarchies to do performance testing with. I don't know if he's added some code for that in the second edition or not because I've not read it. Feel free to use the one from this article if you'd like.
--Jeff Moden
Change is inevitable... Change for the better is not.