Indexes : Indexes are the only best way to improve the performance of select query.
Even though we have indexes, we might not be using them properly. For example, if the select statement has a where condition, we have to check if all the columns in where clause have an index.
Even if one column doesn't have an index, that causes lot of performance degradation. We can even have index on more than one column. Even this helps some times... (covered index - when all the columns in select query have an index, its called covered index)
SQL server has an option even to specify which index we can use when one column has more than one index on it, one for the column alone , one could be on a group of columns.
2. In the where clause if there is any aggregation - for ex: if you have convert(varchar,date1,101) in where clause - then the Index on date1 field doesn't work even though there is an index on it.
Never use functions in the where clause - this causes lot of performance degradation.
