Joe Celko's SQL for Smarties, 3rd Edition

28.2. The Path Enumeration Model

The next method for representing hierarchies in SQL was first discussed in detail by Stefan Gustafsson on an Internet site for SQL Server users. Later, Tom Moreau and Itzik Ben-Gan developed it in more detail in their book Advanced Transact-SQL for SQL Server 2000 (Moreau and Ben-Gan first edition was October 2000). This model stores the path from the root to each node as a string at that node.

Of course, we purists might object that this is a denormalized table, since the path is not a scalar value. The worst-case operation you can do in this representation is to alter the root of the tree. We then have to recalculate all the paths in the entire tree. But if the assumption is that structural modifications high ...