Hierarchical Table Indexing Strategies
You can create a depth-first index or a breadth-first index (or both) on your hierarchical tables. The two types differ in how SQL Server physically stores node references in the index. Defining depth-first and breadth-first indexes can have a significant impact on performance for accessing data in hierarchical tables.
As their names imply, depth-first indexing stores parent and child node references near each other, whereas breadth-first indexing stores references for nodes at the same hierarchical level near each other. Therefore, you will choose the appropriate type of index based on an understanding of how your hierarchical data is shaped in the table, how it will grow, and how it will be typically queried ...
Get Programming Microsoft® SQL Server® 2012 now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
