O'Reilly logo

Inside Microsoft® SQL Server™ 2005: T-SQL Querying by Dejan Sarka, Lubor Kollar, Itzik Ben-Gan

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required