Index Covering

What is Index Covering?

Index covering means to add a non-clustered index on every column  which are used in Query. Easy solution to improve query performance

 

Scenario: Speed Up a Query for a Table with a Clustered Index

Consider a very typical scenario: you have a table EmployeeSalary table with a clustered index on Employee_ID column. You need to speed up a select query quickly:

SELECT Salary_DATE, SUM(AMOUNT) FROM EmployeeSalary GROUP BY Salary_DATE

 

The query's execution plan is quite simple: the database engine scans the whole clustered index, and then it sorts the intermediate result set to satisfy the GROUP BY clause.

 

Can a non-clustered index speed up the query? Definitely. Just create a non-clustered index that contains all the columns used in the query:

CREATE  INDEX sal_amt ON dbo.EmployeeDetail(Salary_DATE, AMOUNT)

 

Re-execute the query it will run faster than the previous query.  If you, look at the execution plan: the query accesses only the index salary_amt; it doesn't touch the table at all. In fact, there is no need to access the table because all the columns necessary to satisfy the query are already stored in the index. This is called index covering.