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 ...

Get Microsoft® SQL Server™ 2000 Programming by Example now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.