Guidelines for Using Indexes

Placing an index on a column often makes the difference between a quick response to a query and a long wait. However, building an index takes time and storage space.

For example, nonclustered indexes are automatically re-created when a clustered index is rebuilt.

Additionally, inserting, deleting, or updating data in indexed columns takes longer than in unindexed columns. However, this cost is usually outweighed by the extent to which indexes improve retrieval performance.

When determining whether or not to create an index, following these general guidelines:
In some cases, indexes are not useful:

If the system does have to search an unindexed column, it does so by looking at the rows one by one. The length of time it takes to perform this kind of scan is directly proportional to the number of rows in the table.