Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a nonclustered index (called NCIDX1) on col1, col2 of a table. I included col3, col4, col5, col6 as included columns in this order.

If I run the following query:

select 
     col1,col2 
from 
     tbl1
where 
     col3 = something
and
     col4 = something
and
     col5 = something

Is it any different than running:

select 
     col3,col2 ,col4
from 
     tbl1
where 
     col1 = something
and
     col5 = something

?

What I am trying to say is, when we create a covering index like the above, and we change the order in which we are accessing data, will it still take advantage of this covering index?

Thanks in advance

share|improve this question
add comment

migrated from serverfault.com Mar 28 '11 at 0:44

This question came from our site for professional system and network administrators.

1 Answer

up vote 7 down vote accepted

Generally, an index should be on

(equalitycol1, equalitycol2, ..., nonequalitycol1, , nonequalitycol2, ...)
INCLUDE
(outputonlycol1, outputonlycol2, ...)

Simply (ignoring ranges) this breaks down into

  • equalitycol: WHERE equalitycol = something. Most selective first. SARGable. Seeks.
  • nonequalitycol: WHERE nonequalitycol <> something. Residual filters
  • outputonlycol: SELECT outputonlycol. No filtering or ordering

ORDER BY and GROUP BY columns will be equality or non-equality columns

In this case...

Query 1 doesn't match the index at all, really, so most likely won't use the index. However, the optimiser may decide it's cheaper to use the index then use other table access such as a table scan or a RID/bookmark lookup.

For query 2 the optimiser may decide to use the index because col1 would be an equality match. And then use the unsorted col5 from the INCLUDE. YMMV, but there is a higher chance.

share|improve this answer
    
Thanks for the clarification. the order of equality columns (amongst themselves) and non equality columns (amongst themselves)... does it matter? like where equalitycol2='' and equalitycol1='' –  Manjot Mar 28 '11 at 20:28
    
@Manjot: For the WHERE clause order, no: the optimiser works it out. Two index (col1, col2) and (col2, col1) are different though. Note (col1, col2) will be contained by (col1, col2, col3, col4) though so the first isn't needed. –  gbn Mar 29 '11 at 4:57
    
Thank you very much. –  Manjot Mar 29 '11 at 20:52
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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