Indexing strategy using rowstore indexes
Now that we’ve covered the basics of how rowstore indexes are structured and how they are used to access data, let’s move on to where and when they should be used, along with some best practices for efficient index design.
The goal of an indexing strategy is to minimize the amount of I/O required to satisfy the queries being generated against the database. This translates into a few simple guidelines:
- Keep indexes as small as possible. The more rows that fit on a page, the fewer page reads that are required to access the data.
- Avoid lookups – they add unnecessary I/O and can sometimes lead to suboptimal query plans.
- Choose index keys that support query predicates so that indexes can be used for seeks rather than scans.
- When creating indexes with multiple key columns, columns used for equality comparisons should be first, followed by columns used for inequality comparisons. The leading column should...