Effective indexing

Finally, we’ve come to the most important part of using this type, and that’s an indexing strategy. All of the examples you’ve seen in this chapter are nothing without the right index on the HierarchyId field. It’s very important! Many developers don’t understand the main idea of using this type. They think that it’s a great tool for designing and using hierarchies in a database. And all you need to do is to write a query, and you’ll get your data quickly and effectively. That’s totally wrong! It’s not the full picture. You need to understand HierarchyId queries and build correct indexes to achieve great performance. You can index a HierarchyId field in two ways: in depth or in breadth. Your choice of index type depends on your queries. We’ll consider them further in a small example.