Determining What Makes a Bad Index

Now that you know what makes a good index, let’s investigate what makes a bad index. There are several “gotchas” to be aware of:

• Using unsuitable columns
• Choosing unsuitable data
• Including too many columns
• Including too few records in the table

Using Unsuitable Columns

If a column isn’t used by a query to locate a row within a table, then there is a good chance that the column won’t need to be indexed, unless it is combined with another column to create a covering index, as described earlier. If this is the case, the index will still add overhead to the data-modification operations but will not produce any performance benefit to the data-retrieval operations.

With clustered indexes, as I have mentioned before, columns that are modified frequently are not ideal candidates for forming the index.