What is SQL Server Covering Index? How does it work?

+1 vote
asked Jun 13, 2013 in SQL Server by April Miracle

I am not able to understand what is covering index and how does it work internally. Why should I use it? Does it increase performance?

Share

1 Answer

+1 vote
answered Jun 14, 2013 by Aadhira (1,213 points)
selected Jun 18, 2013 by administrator
 
Best answer

Usually, indexing concept is used to increase the performance of the select query. If your application is going to do so much of select operation to get bulk result, then the best way to do is indexing.

There are well know 2 types of indexing are Clustered index and non clustered index.(With latest updates we have more than that.)

Clustered index will create index on a given column. When it creates the index, it will have the complete row information with that. So, once you search value on the clustered index column, it will be able to get all values from the row.

But Non clustered index is different. It will hold the columns on which we created non clustered index also, it will have primary key of the row. So, if you try to retrieve value from any column which are not included in the Non Clustered Index, but if your WHERE clause is used some condition on the Non Clustered column, then what it will do is,

First it will go to Non Clustered Index and find the matching entry based on your WHERE condition. Then, since the Non clustered index will not have the columns you have mensioned in the SELECT portion, it will use the primary key reference to go to the main table to find the respective values.

So, it is doing additional look up to get the values which will cause delay in performance.

To avoid this, you can use covering index. What covering index is going to do is, Same non clustered index will have indexed column as well as the columns which want to retrieve in the SELECT query. But, those additional columns will not be used in the Indexing process.

Ex:

    CREATE TABLE #CustomerTempTable(
    CustomerID int identity Primary key,
    Name varchar(50), 
    Age int)

    INSERT INTO #CustomerTempTable VALUE('Ashok', 25)
    INSERT INTO #CustomerTempTable VALUE('Mark', 38)
    INSERT INTO #CustomerTempTable VALUE('Victor', 26)
    INSERT INTO #CustomerTempTable VALUE('Menon', 28)
    INSERT INTO #CustomerTempTable VALUE('Frank', 32)
    INSERT INTO #CustomerTempTable VALUE('Gene', 34)
    INSERT INTO #CustomerTempTable VALUE('Joseph', 38)

    CREATE NONCLUSTERED INDEX ageIndex ON #CustomerTempTable(Age) 

    SELECT Name, Age FROM #CustomerTempTable
    WHERE Age > 30

Just with the Non clustered index on age column, the above select query would have executed in 2 steps. Because your select query has Name and Age. Name field is not included in the Non Clustered index. It is only available in Clustered index which is created by the Primary Key. First, it went to non clustered index and found all fields with age greated than 30 using binary search. Then it would have take primary key reference of all the values it found. Then it went to clustered index created by the primary key and found the matching primary key reference, then found the matching "Name" for the primary key, then displayed Name and Age

Using Covering Index:
Covering index can be created using INCLUDE Clause. Remove the existing ageIndex and create the Covering index using below query.

CREATE NONCLUSTERED INDEX ageIndex ON #CustomerTempTable(Age) INCLUDE(Name)

SELECT Name, Age FROM #CustomerTempTable
WHERE Age > 30

Now the same query executed in different way. Because this time, it does not need to do look up to the Clustered index table.
When it see the WHERE condition, it went to Non Clustered index ageIndex, then found all the values with greater than 30. Since the Non Clusdered index also has the value of Name column, it can get the name from the index itself. That way it avoided additional step.

Definitely, it increases the performance now.

Your answer

Preview

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.
Anti-spam verification:
To avoid this verification in future, please log in or register.
site design / logo / content © 2013 - 2015 pinfaq.com
...