A good index helps you to retrieve your data by using fewer I/O operations
and system resources than a table scan.
Because an index scan requires traversing the tree to find an individual value,
using an index is not efficient when you are retrieving large amounts of data.
Note:
If a query accesses more than 20 percent of the rows in a table,
a table scan is more efficient than using an index.
An effective index retrieves only a few rows
- in fact, most queries end up using only a few rows anyway.
To perform effectively, an index should be designed with good selectivity.
The selectivity of an index is based on the number of rows per index key value.
An index with poor selectivity has multiple rows per index key value;
an index with good selectivity has a few rows or one row per index key value.
A unique index has the highest selectivity.
The selectivity of the index is stored within the index distribution statistics.
You can view the selectivity of an index by using the command DBCC SHOW_STATISTICS.
An index with good selectivity is more likely to be used by Query Optimizer.
You can enhance the selectivity of an index by using multiple columns to create a composite index.
Several columns with poor selectivity can be joined in a composite index
to form one index that has good selectivity.
Although a unique index provides the best selectivity,
be sure to choose an index type that fits your data model.
For example,
if you have several entries for the last name "Smith" in a Customers table,
you won't be able to create a unique index on last names,
but you might still find such an index useful.
Source:
Microsoft SQL Server 2000 Administrator's Companion eBook