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.

Advertisements

2 thoughts on “Index Covering

Thanks for the comment, will get back to you soon... Jugal Shah

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s