Composite non-clustered index: will it still work if I use only one index col) ?

  • VoldemarG

    Hall of Fame

    Points: 3686

    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...

    Voldemar likes to play CHESS (and IS good at it!)

  • frederico_fonseca

    SSChampion

    Points: 14756

    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

  • Jonathan AC Roberts

    SSCoach

    Points: 17337

    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.

  • Grant Fritchey

    SSC Guru

    Points: 396716

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 4 posts - 1 through 4 (of 4 total)

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