Post reply

which one to go with (normal index vs Composite index) ?

  • April 2, 2014 at 5:12 am

    #306035

    Hi there ...

    Which one you will prefer & why you prefer it ?

    create index idx on TableName (col1,col2,col3)

    or

    create index idx1 on TableName (col1)

    create index idx2 on TableName (col2)

    create index idx3 on TableName (col3)

    Thanks in advance

  • April 2, 2014 at 5:21 am

    #1702391

    Absolutely impossible to say.

    Indexes are created to support the queries that run against that table. Hence the queries will determine which (if either) of those index options are appropriate.

    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

  • April 2, 2014 at 7:23 am

    #1702456

    If I have a query that searches:

    WHERE Col1 = @val1 AND @Col2 = @val2 and @Col3 = @val3

    It's pretty likely that first index is better. But, if I have a query that searches

    WHERE Col2 = @val2 and @Col3 = @val3

    It's pretty unlikely that the first index is better, but, the three indexes are not likely to be better either. There are just tons and tons of factors around indexes that determine their use.

    "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

  • April 2, 2014 at 10:37 am

    #1702554

    I tend to prefer fewer indexes, but with composites and includes, but as Gail and Grant mentioned, those have to be based on your queries. There's no way to decide on indexes for a table without knowing the workload.

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

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