From: http://www.richard-quinn.com/quinn-pages/mssql_achieving_better_performance.html
- Does the table contain a column uniquely identifying the row?
This is a primary candidate for a clustered index.
- Will the data be undergoing large numbers of modifications compared to reads? (a ratio of 1:2 or higher)
Be careful of performance, add few indexes.
- Will the table become very large? (>100.000 rows)
Be careful of the indexes disk usage. Index all fields used in where or sort clauses.
- Do most queries sort on a particular field?
Index this field! Sort the index the way your sorts expect the data.
- Do most queries filter on a particular field?
Index this field! If the query selects a few columns, create a composite index and add the selected fields to the index.
- Does the table contain mainly natural language text?
Consider a full-text index.