Covering indexes question

[jconner]

If I have an index on 3 columns, and a user submits a query that uses only one of the columns, will SQL Server use the 3 column index or would you have to have an index on the single column alone? We use a lot of covering indexes, but occasionally get queries that use one or two of the columns that are covered by a multi-columned index...was wondering if the index would be used or not.

Thanks!

 

[LFCfan]

Hi there

Say you have an index on columns ColA,ColB,ColC (in that order)
and a query

SELECT * FROM MyTable
WHERE ColB = 'whatever'

then the index will not be used
In the following case:

SELECT * FROM MyTable
WHERE ColA = 'whatever'

and also in this case:

SELECT * FROM MyTable
WHERE ColA = 'whatever'
AND ColC = 'whatever2'

the index will be used

Basically, unless one of the fields queried on is the first one in the composite index, it wont be used
(also i believe it needs to be in the same order in the WHERE clause as it is defined in the composite index)
Hope this is of some help...

 

[jconner]

Thanks for the info! That helps a lot...exactly the info I needed!

On the last part, about the columns in the where clause needing to be in the same order as the index - I thought the optimizer would hanlde that? I'll look to see if I can confirm what you said about that.

Thanks for your help!

 

[nigelrivett]

>> (also i believe it needs to be in the same order in the WHERE clause as it is defined in the composite index)
not true - the order is unimportant unless the force order option is used.

Given that the question is about covering indexes in the above example it will depend on the data distribution as to whether the index will be used. e.g.
SELECT * FROM MyTable
WHERE ColA = 'whatever'

would probably table scan if a lot of the entries are 'whatever' as it has to retrieve the data page anyway so would probably end up getting all the pages into memory.
It uses statistics to decide whether or not tto use the index.

SELECT * FROM MyTable
WHERE ColB = 'whatever'

wouldn't use the index but if it was covering
SELECT ColC, ColB FROM MyTable
WHERE ColB = 'whatever'

It would probably scan the index as the data is all contained within the leaf level and so would mean less reads than scanning the data pages.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[LFCfan]

thanks for the clarification nigel