December 10, 2019 at 6:33 pm
If I have an index called idxT1_C1C2C3 on table T1 that is non-clustered non-unique non covering, has 3 colums C1, C2, C3 in same order,
and I run a query
SELECT <column list......, 50 columns...> FROM T1
WHERE C1 = 'value'
can such query be expected at all to still use the idxT1_C1C2C3 index? Or provide any benefits to the query at all ? Or should I definitely create a separate index idxT1_C1 ? (on C1 column only, no matter covering or not...).
Sorry for a too simple question...
Likes to play Chess
December 10, 2019 at 6:42 pm
why don't you try it and look at the resulting explain plan? That should have been your first call before asking on forums.
but answer would normally be yes it will use the index - but it could also be the case that the data distribution for that column makes the engine decide that a table scan is faster
December 10, 2019 at 7:05 pm
It may or may not use the index. It depends on whether the optimiser thinks a full table scan will be more efficient than a seek with a key-lookup. An index just on C1 would be the same as it also would involve a seek and a key-lookup, so not much difference. If it is really more efficient to use the index but SQL Server is deciding not to then you might be able to persuade it to by adding statistics on the T1(C1) column. Or you could INCLUDE all the columns on T1 that are in your SQL Statement in the index idxT1_C1C2C3.
December 11, 2019 at 1:47 pm
Under most circumstances, not getting into data skew, bad column choices, etc.
Yeah.
In fact, having an index on C1 or an index on C1 and C2 as keys would be effective duplicates of C1, C2, C3 as keys. I would strongly, in most situations, insert several caveats, recommend against that.
However, like has already be stated, check the execution plan to be sure. You may be in one of the edge cases. We, and you, can't possibly know without testing.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply