An index that contains all information required to resolve a query without additional ‘lookups’.
Covering indexes are the same as basic non-clustered database index, except for the fact that they actually contain the required data and can return it.
In most queries, the SQL engine finds the necessary rows in the index and then goes to the underlying table to get the data. If the index also contains the data fields, as is the case with a covering index, then the SQL engine doesn’t need to go to the underlying table for the data, it gets it all from the index itself. This is a technique used to improve performance.
These indexes are based off of a key column, but then are also able to include non-key columns needed by the query. By creating an index that encapsulates all columns of a query, you are essentially creating a much smaller dataset than the table itself.
During the execution of the query, the engine can now focus all its resources on the smaller index to find the associated rows without even touching the actual database table(s) listed in the query. This then requires much less logical and/or physical disk I/O and conversely reduces the time taken to retrieve the rows.
Typically and index in restricted to 16 key columns. By including non-key columns to your index, you can now have up to 1023 included columns
The SQL engine does not need to go to the underlying table(s) for data rows.
As there is with any performance tuning technique, there are some disadvantages to be aware of.
Having many non-clustered indexes can slow down the Insert/Update/Delete actions on that table
Datatypes text, ntext and image cannot be used as non-key columns within the index
Non-key columns cannot be dropped from the table unless the index is dropped first
Disk space is required to store the index itself. Information will now reside in both the index and table
Using a basic filtered query, we can see the execution plan with and without indexes in play.
SELECT ContactID, FirstName, LastNameFROM ContactMaster where LastName > 'Coates' order by FirstName
By implementing a basic covering query, we just reduced the logical reads by over 450%. The database engine will no longer need to perform a full table scan, and can rely solely on the index to perform the scan.