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

Materialized Path

So far I presented solutions where paths were computed when the code was executed. In the materialized path solution, the paths will be stored so that they need not be computed repeatedly. You basically store an enumerated path and a level for each node of the tree in two additional columns. The solution applies only to trees—possibly forests.

There are two main advantages of this approach over the iterative/recursive approach. Queries are simpler and set-based (without relying on recursive CTEs). Also, queries typically perform much faster, as they can rely on indexing of the path.

However, now that you have two additional attributes in the table, you need to keep them in sync with the tree as it undergoes changes. The cost of ...