0

I understand that covering index will speed up read operation if all the columns in select list are covered in the index.

But what if I select and filter on columns in other tables as well? Would covering index still help in this case?

Here is my thought: Since the join would force the clustered key to be matched, so the clustered key look up couldn't be avoided. And by finding the clustered key we would have all the information we need thus covering index wouldn't help to speed up the read.

3

Not sure what you mean by:

the join would force the clustered key to be matched.

Non-clustered indexes include the clustered index key (needed as the unique row locator). Consequently, a non-clustered index can cover a query as long as the query references only columns in the clustered index key, non-clustered index key columns, and included columns.

A potentially covering non-clustered index might not be used if other indexes are available to process the query more efficiently depending on the columns specified in WHERE, JOIN, or ORDER BY clauses.

4
  • so it means covering index might still be useful if the query contains join?
    – Steve
    Apr 23 '16 at 23:24
  • @Steve, yes, a query with a join can use a covering index to avoid a clustered index key lookup.
    – Dan Guzman
    Apr 24 '16 at 3:26
  • so if I include all columns in select list in corresponding table's index which I used to filter on will eliminate key lookup correct?
    – Steve
    Apr 24 '16 at 19:18
  • @Steve, as long as the covering index is used, the execution plan will not need a key lookup.
    – Dan Guzman
    Apr 24 '16 at 21:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.