August 24, 2014

What is Covering Index in SQL

A covering index is a non-clustered index which includes all columns referenced in the query and therefore, the optimizer does not have to perform an additional look-up to the table in order to retrieve the data requested. As the data requested is all indexed by the covering index, it is a faster operation.

covering index is a non-clustered index which stores additional columns at leaf level to avoid bookmark lookup to heap/clustered index.this is typically created using INCLUDE clause.

Let say we have a table tab1 and we have columns starts from column 1 to 10. and we have clustered index on column1 and non clustered index on column 2 . for example you written a query select * from tab1 where column 2=200. this query first will go and look for the matching data in non clustered index and because of our query want to retrieve all the columns it will perform key look up , not helathy. this time i had one more non clustered index on column3 and column4 ,composite.in this case our query select column3,column4 from tab1 where column3 =200 no need to check in clustered index and table it can retrieve the data from non clustered index(in this case covering) index.

No comments:

Post a Comment