Inside Microsoft® SQL Server™ 2005: T-SQL Querying

Nested Sets

Nested sets is one of the most beautiful and intellectually stimulating solutions I’ve ever seen for modeling trees.

More Info

Joe Celko has extensive coverage of the Nested Sets model in his writings. You can find Joe Celko’s coverage of nested sets in his book, Joe Celko’s Trees and Hierarchies in SQL for Smarties (Morgan-Kaufmann, 2004).

Here I will cover T-SQL applications of the model, which for the most part work in SQL Server 2005 only because they use new features such as recursive CTEs and the ROW_NUMBER function.

The main advantages of the nested sets solution are simple and fast queries, which I’ll describe later, and no level ...