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?
Also, the order of equality columns (amongst themselves) and non equality columns (amongst themselves)… does it matter? Like where equalitycol2='' and equalitycol1=''
.
Best Answer
Generally, an index should be on
Simply (ignoring ranges) this breaks down into
WHERE equalitycol = something
. Most selective first. SARGable. Seeks.WHERE nonequalitycol <> something
. Residual filtersSELECT outputonlycol
. No filtering or orderingORDER BY
andGROUP 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 unsortedcol5
from theINCLUDE
. YMMV, but there is a higher chance.For the
WHERE
clause order, no: the optimiser works it out. Two indexes(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.