Covering indexes in joins

Question

Covering indexes in joins

Imke Cronje

SSCarpal Tunnel

Points: 4826
More actions
July 18, 2016 at 3:40 am

#328300

Hi Guys,
I have a question about using covering indexes within joins. If I use 1 column in a covering index and use it in a joins, I get a scan within my query plan.
Should I include all the columns in my joins that reference the covering index? The index has 3 columns but no 'includes' columns.
Regards
IC

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

What kind of scan - table scan, clustered index scan, or non clustered index scan? Please post the actual execution plan.

John

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 2

Further on John's reply, we need the table structures, the query and the actual execution plan, without the information it's only wild guesses.

😎

Imke Cronje SSCarpal Tunnel Points: 4826 More actions · Answer 3

Don't worry....I already figured it out.

Its a clustered index scan and when I include both indexes in the joins it returns a Seek instead of a Scan.

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 4

Imke Cronje (7/18/2016)
Don't worry....I already figured it out.
Its a clustered index scan and when I include both indexes in the joins it returns a Seek instead of a Scan.

Now all you have to worry about is how many times it does the seek. Seriously. See the properties window on the icon.

As a bit of a sidebar, a seek on the execution plan is not always better than a scan, especially if a seek is done once for each row.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)