Choice of index to use

Question

Choice of index to use

nadersam

SSCertifiable

Points: 6542
More actions
November 24, 2009 at 2:16 am

#385840

Dear All,
I faced a very strange issue that I cannot understand at all.
The current db design is as follows
Orders table containing Field1 varchar (50), Field2 int and many other fields.
The indexes I am asking about are as follows:
Idx1 clustered index on Field1, Field2.
Idx2 index on Field1.
When I run this sql : select * from orders where Field1='123', the idx1 is used,
Select Field3 from orders where Field1='123', the idx1 is used,
Select Field1 from orders where Field1='123', the idx2 is used.
My point is when selecting any or all of the fields contained in the clustered index , the normal index (idx2) is used, while when selecting any other field in the table the clustered index is used.
I hope my point is clear and looking forward for you replies.
Thanks in advance
Nader

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

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

joeroshan SSChampion Points: 10377 More actions · Answer 1

The clustered index columns are automatically included in non-clustered index. What is the size of the table? If the columns are not found in the index it has to do a key lookup, If this is more expensive, sql automatically go for clustered index scan.

----------------------------------------------------------------------------------------------------------------------------------------------------
Roshan Joe

Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

nadersam SSCertifiable Points: 6542 More actions · Answer 2

Thanks for ur reply,

The table size is about 400 mb.

based on ur reply i think sql server used the clustered index seek becuase the fields were not included in index but for first sql where i select one or both of the fields contained in the clustered index , it used the other index not the clustered one so why is that.

Also pls tell me if its better to use the clustered or normal index ?

i attached images of execution plan but not sure it will show properly in forum.

Thanks

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

It's using the clustered index when you do SELECT * because the cluster contains all the columns and you're asking for all the columns. If it used the nonclustered index it would have to do a lookup to the cluster, which is expensive.

Why do you have a nonclustered index on the same columns as the cluster? That's usually a sub-optimal design.

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

nadersam SSCertifiable Points: 6542 More actions · Answer 4

I appreciate ur reply.

I found this by Coincidence, i was looking for duplicate indexes, then this came out.

Before i delete it i wanted to make sure its not used anywhere for any reason but from index usage tables i found it's used so i needed to know if its better to remove that index or what?.

Thanks again

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

Unless you have a really good reason for wanting a duplicate index, or there's a major performance degradation when you remove it, take it out. SQL will use the cheapest index available for a query, and the nonclustered may be very slightly cheaper for queries that it covers than the clustered index is.

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

nadersam SSCertifiable Points: 6542 More actions · Answer 6

Thanks very much for all replies.

They were very informative