Indexes can enhance performance because they can provide a quick way for the query engine to find data. However, you must also take into account whether and how much you’re going to be inserting, updating, and deleting data. Following are some guidelines when designing database indexes:
•For tables that are heavily updated, use as few columns as possible in the index, and don’t over-index the tables.
•If a table contains a lot of data but data modifications are low, use as many indexes as necessary to improve query performance.
•For clustered indexes, try to keep the length of the indexed columns as short as possible. Ideally, try to implement your clustered indexes on unique columns that do not permit null values.
•The uniqueness of values in a column affects index performance. In general, the more duplicate values you have in a column, the more poorly the index performs