Indexes are best suited for tasks such as the following:
* Queries that specify a narrow search criteria
These queries should retrieve only a few rows that match the specific criteria.
* Queries that specify a range of values
These queries should also retrieve a small number of rows.
* Searches that are used in a join
Columns that are often used as join keys are good candidates for indexes.
* Searches that retrieve data in a specific order
If the resulting data set is to be sorted in the order of a clustered index,
the sort is not necessary, because the resulting data is returned presorted.
For example,
if the clustered index is on the lastname, firstname columns
and the application requires sorting by last name and then first name,
it is not necessary to add the ORDER BY qualifiers.
Indexes should be used cautiously and sparingly on tables
that have a large number of insert, update, and delete operations performed on them
because each operation that changes the data must update the index pages as well.
Source:
Microsoft SQL Server 2000 Administrator's Companion eBook