Index covering can produce dramatic performance improvements when all columns needed by the query are included in the index.
You can create indexes on more than one key. These are called composite indexes. Composite indexes can have up to 31 columns adding up to a maximum 600 bytes.
If you create a composite nonclustered index on each column referenced in the query’s select list and in any where, having, group by, and order by clauses, the query can be satisfied by accessing only the index.
Since the leaf level of a nonclustered index or a clustered index on a data-only-locked table contains the key values for each row in a table, queries that access only the key values can retrieve the information by using the leaf level of the nonclustered index as if it were the actual table data. This is called index covering.
There are two types of index scans that can use an index that covers the query:
The matching index scan
The nonmatching index scan
For both types of covered queries, the index keys must contain all the columns named in the query. Matching scans have additional requirements.
“Choosing composite indexes” describes query types that make good use of covering indexes.
