Covering Indexes

Question

Covering Indexes

John Doe-446893

Right there with Babe

Points: 714
More actions
December 21, 2007 at 11:43 pm

#182160

Does order of columns matter on covering indexes? If does, what order should it be in for example; select list, where clause then join condition Or any other order?

Viewing 7 posts - 1 through 6 (of 6 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Very important. Queries can only seek on an index if the columns that are in the filter are a left-based subset of the index.

Look at it this way. If you take a telephone directory, you can think of that as an index of all people in a city/region. The index columns are (at least in mine) surname, firstname, address, phone number.

So we may have a list (index) like this

Brown, Matthew, 271 West Street, 738-2999-29291-3333

Brown, Robert, 12 Short Road, 2773-3838-2722

Brown, Robert, 53-3 Adelaid Street, 454-6888-2035

Brown, Zach, 99 114th Road, 283-0222-3434

Buchanan, Jeff, 2b Walton Crescent, 333-444-55555

Burch, Michael, 342 Friar's Wharf, 54-888-22220

Burton, David, 77 Turn Again Lane, 583-3222-2322

Burton, Jeff, 22 Castle Road, 566-5551-7111

To find all people with a surname Burton is easy (index seek on the left-most column of the index). To find all the people named Robert Brown is also very easy (index seek on the left-most 2 columns of the index). To find all the people named Jeff required that you read the entire index entry by entry (index scan, since the column been searched on is not the left-most column)

Does that make sense?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

John Doe-446893 Right there with Babe Points: 714 More actions · Answer 2

Thank you for your insight. That makes sense but I am trying to create a covering index which will include all the columns in select list, join condition and where clause. This particular proc is used frequently and it is doing index scan on the table right now. My goal is to achieve perfomance gain using covering index. If you have to use all columns mentioned above in what order will you use it?

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 3

A covering index could certainly help... but there's a lot of things that will change an Index Seek into and Index Scan... usually non-sargeable WHERE clauses and ON clauses. Also, a lot of folks make the mistake of trying to do everything in one or two queries in a proc. If the proc is really that valuable, I'd suggest some extreme "look again" at the code... test each query... look for hidden RBAR and other non-setbased problems. Ensure that every WHERE/ON is fully sargeable. Then, you can start to worry about indexes.

--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)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Ok, so given what I said above, if, using the example I wrote, you had to find a person with a specific phone number, how would you like the phone book to be arranged?

(SELECT surname, firstname from TelephoneDirectory where PhoneNumber=@PhoneNumber)

Surname, firstname, address, phone number?

Firstname, surname, phonenumber, address?

Phonenumber, surname, firstname, address?

Some other order?

p.s. Can we see the query?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

goetz.gaertner SSC Journeyman Points: 82 More actions · Answer 5

Okay, for this sample

SELECT surname, firstname from TelephoneDirectory where PhoneNumber=@PhoneNumber)

Surname, firstname, address, phone number?

Firstname, surname, phonenumber, address?

Phonenumber, surname, firstname, address?

I suggest an index like this

CREATE NONCLUSTERED INDEX(PhoneNumber) INCLUDE(surname, firstname)

Something like this. But there are much more things to consider.

If you have already a clustered Primary Key , which is an Clustered Index,

you have the columns of this already in any Nonclustered Index. So you

don't have to include these columns of the Clustered Index into the nonclustered

Index because they will already be there.

So to give really an advise is diffcult to say depends on the queries issued against the table

and the tables structure. A good thing is to read the Stairway to Indexes the first few levels from 1 to 5

should give you some idea's of the problem and i think is a good explantion of this topic.

There is not really a big general rule. But some basic things you should consider and these basic will be covered in the

Stairways article.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

goetz.gaertner (10/20/2011)
If you have already a clustered Primary Key , which is an Clustered Index,
you have the columns of this already in any Nonclustered Index. So you
don't have to include these columns of the Clustered Index into the nonclustered
Index because they will already be there.

Primary key != clustered index.

You should always specify the columns you need in an index and not rely on SQL implicitly doing it for you. What happens if someone moves the clustered index?

p.s. 4 year old thread, and SQL 2000 does not have include columns.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass