You should follow a number of index guidelines
to increase both the efficiency and the performance of the system:
* Use indexes in moderation.
A few indexes can be quite useful,
but too many indexes can adversely affect the performance of the system.
Because the indexes must be maintained,
every time an insert, update, or delete operation is performed on the table,
the index must be updated.
If there are very many of these operations, the overhead of maintaining the index can be quite high.
* Don't index small tables.
It is sometimes much more efficient to perform table scans if the table is small (say, a few hundred rows).
The additional overhead of the index is not worth the benefit.
* Use as few index key columns as necessary to achieve good selectivity.
The fewer columns, the better, but not at the expense of selectivity.
An index with a few columns is called a narrow index,
and an index with many key columns is called a wide index.
Narrow indexes take up less space and require less maintenance overhead than do wide indexes.
* Use covering queries whenever possible.
A covering query is one in which all of the desired data is held in the index keys
- that is, all of the index keys are also the selected columns.
With a covering query, only the index is accessed; the table itself is bypassed.
A covering index is an index in which all of the table columns are included.
For example,
if the index is on columns a and b and c and the SELECT statement
is requesting data from only those columns, only the index needs to be accessed.
Source:
Microsoft SQL Server 2000 Administrator's Companion eBook