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