Post reply

is there any difference among the indexes when the index filed sequence is not the same?

  • October 22, 2009 at 6:47 pm

    #135879

    There are three indexs ,the unique difference is that the index fields sequence is different among the indexs,

    but I only create one kind of index to query the same SQL statement on the table at a time,the SQL Statements is

    as below,the sequence of the index fileds in the three kinds of index , please see below,Now I want to use the same

    SQL statement to Query, which kind of index will be more effective?

    SELECT * From table1 where ITNO='A00068' and LotNumber='100' and [Month]='08'

    Index 1. ITNo、LotNumber、Month

    index 2. LotNumber、ITNo、Month

    Index 3. Month、ITNo、LotNumber

    Thanks in advance!

  • October 23, 2009 at 3:44 am

    #1069884

    If you're doing an equality match on all three columns, then any one of those three indexes is fine. If you're doing matches on some of those columns, then not all three will be useful.

    See

    http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/

    and

    http://sqlinthewild.co.za/index.php/2009/02/06/index-columns-selectivity-and-inequality-predicates/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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