Summary

This chapter covered a lot of ground, so let’s review the overall indexing strategy guidance:

1. Clustered index data access is generally more efficient than heaps and every table in the database should have a clustered index, except for short-lived tables such as staging tables.
2. Create clustered indexes first based on the data structure. These should generally be primary keys unless there’s a specific reason to cluster a different column or columns (for example, surrogate versus natural keys).
3. Create non-clustered indexes on all foreign key columns.
4. Once you begin writing queries, create additional non-clustered indexes to support the application queries, or add additional columns to existing foreign key indexes.
5. Create covering indexes where practical, balancing overhead with performance.
6. Do not over-index heavily updated tables; balance the cost of index maintenance with the benefit to queries. Just because the SQL Database Engine allows...