ASP.NET Tips #15 - SQL Index tips

Indexing tables is not an exact science. It requires some trial and error combined with lots of testing to get things just right. Even then, the performance metrics will change over time as you add more and more data.

1. You get exactly one clustered index on a table. Ensure you have it in the right place. First choice is the most frequently accessed column, which may or may not be the primary key. Second choice is a column that structures the storage in a way that helps performance. This is a must for partitioning data.
2. Clustered indexes work well on columns that are used a lot for 'range' WHERE clauses such as BETWEEN and LIKE, where it is frequently used in ORDER BY clauses or in GROUP BY clauses.
3. If clustered indexes are narrow (involve few columns) then this will mean that less storage is needed for non-clustered indexes for that table.
4. You do not have to make the primary key the clustered index. This is default behavior but can be directly controlled.
5. You should have a clustered index on every table in the database. There are exceptions, but the exceptions should be exceptional.
6. Avoid using a column in a clustered index that has values that are frequently updated.
7. Only create non-clustered indexes on tables when you know they’ll be used through testing. You can seriously hurt performance by creating too many indexes on a table.
8. Keep your indexes as narrow as possible. This means reducing the number and size of the columns used in the index key. This helps make the index more efficient.
9. Always index your foreign key columns if you are likely to delete rows from the referenced table. This avoids a table scan.
10. A clustered index on a GUID can lead to serious fragmentation of the index due to the random nature of the GUID. You can use the function NEWSEQUENTIALID() to generate a GUID that will not lead to as much fragmentation.
11. Performance is enhanced when indexes are placed on columns used in WHERE, JOIN, ORDER BY, GROUP, and TOP. Always test to ensure that the index does help performance.
12. If a non-clustered index is useful to your queries, but doesn't have all the columns needed by the query, you can consider using the INCLUDE option to store the extra columns at the leaf level of the index.
13. If temporary tables are in use, you can add indexes to those tables to enhance their performance.
14. Where possible, make the indexes unique. This is especially true of the clustered index (one of the reasons that the primary key is by default clustered). A unique index absolutely performs faster than a non-unique index, even with the same values.
15. Ensure that the selectivity of the data in the indexes is high. Very few unique values makes an index much less likely to be used well by the query optimizer.
16. It is almost always better to let SQL Server update statistics automatically.