Non clustered indexes

Hi Ravi, The fewer indexes you have - the better. Each index might speed up some queries - but it also incurs overhead and needs to be maintained. Not so bad if you don't write much to the table. If you can combine multiple columns into a single index - perfect! But if you have a compound index on multiple columns, that index can only be used if you use/need the n left-most columns. compound indexes are beneficial and desirable - but only if you can really use them! Having just one index with your all where clause columns does not help you at all, if you need to select the columns individually.

You should generally add non-clustered indexes to columns that are used as foreign keys.