Microsoft® SQL Server™ 2000 Programming by Example

Benefits of Indexes

SQL Server queries can benefit from the existence of suitable indexes in the following cases:

• Exact-match queries—When searching for rows with specific key values. These are queries with a WHERE clause to restrict the query to specific values for every key column.

• Range queries—When solving queries that search for a range of values in a column.

• Filtering for values in the foreign key to solve a join operation—When using a JOIN predicate to search for rows in a table based on keys from a second table.

• Hash and merge join operations—In some cases, having an index can speed up the execution of a JOIN algorithm, because the data is exactly in the order that the JOIN algorithm uses.

• Covering a query—To avoid a full-table scan, when ...