Creating Right Indexes

That's the age old question. I was revisiting the questions today while help a team member in optimizing the SQL query. A report needs to iterate 4 times over a huge table. And that is causing serious performance issues.
Some cardinal rules:

• Create indexes for the column(s) in 'Where' clause. For e.g. "SELECT * FROM Employees WHERE Salary BETWEEN 10000 AND 20000". A index on 'Salary' would help here.
• Keep the indexes as short as possible.
• Select columns which high percentage of unique values for Indexes.
• Consider composite indexes if it avoid full table scan. This is in violation of short indexes. But you need to work on the trade off in your performance testing and tuning.
• Check the execution plan of the query using tools provided by Database vendor
• Indexes can become performance drag if overused. Please note that Indexes needs to be updated whenever data is modified.