Post reply

Additional index needed?

  • July 28, 2011 at 10:15 am

    #254480

    Good day all. This is for the index experts. I haven't tested anything and was hoping someone would have the knowledge. I have a fairly simple table similar to:

    END_DT

    ITEM_NUM

    SLS

    Primary key exists and is END_DT, ITEM_NUM

    My question is this - Most queries will be written with END_DT only in the where clause or ITEM_NUM only in the where clause. Should i build a separate index each for END_DT & ITEM_NUM or does the primary key handle that?? I'm thinking it doesn't but that's why i'm asking.

    Thanks in advance for any help

    Peter

  • July 28, 2011 at 10:18 am

    #1360288

    As Item_Num is not on the leading edge of your index, it could benefit from having an additional index added.

  • July 28, 2011 at 10:19 am

    #1360289

    Queries that filter on End_dt alone can seek on the primary key index. Queries that filter on End_dt and on ITEM_NUM can seek on the primary key index. Queries that filter on ITEM_NUM alone cannot (it's not a left-based subset of the index key)

    If you have queries that filter on ITEM_NUM alone, you'll need an index on ITEM_NUM.

    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

  • July 28, 2011 at 10:21 am

    #1360292

    Edit : Just repeated what others have said.

  • July 28, 2011 at 10:27 am

    #1360299

    Thank you Gail and SSCrazy for the advice.

    As far as the additional request -

    THere could be 5m rows

    156 dates (3 yrs)

    50,000 different items.

    The query distribution is estimated at

    1/3 combined - SKU/WK

    1/3 - SKU only

    1/3 - DATE only

  • July 28, 2011 at 10:29 am

    #1360304

    PeterG-377490 (7/28/2011)

    Thank you Gail and SSCrazy for the advice.

    As far as the additional request -

    THere could be 5m rows

    156 dates (3 yrs)

    50,000 different items.

    The query distribution is estimated at

    1/3 combined - SKU/WK

    1/3 - SKU only

    1/3 - DATE only

    IF SKU = Item_Num,

    then it is a good candidate for an index.

  • July 28, 2011 at 10:31 am

    #1360306

    thanks Steve.......didn't mean to call you CRAZY!!!! Picked up the wrong name.

  • July 28, 2011 at 10:32 am

    #1360309

    With that distribution I'd really go with PK : SKU, endt

    Then clustered on the date.

    Your PK will be way more selective that way.

  • July 28, 2011 at 10:33 am

    #1360310

    PeterG-377490 (7/28/2011)

    thanks Steve.......didn't mean to call you CRAZY!!!! Picked up the wrong name.

    Not the first time it happens! Don't sweat it 😉

  • July 28, 2011 at 10:49 am

    #1360324

    Ninja's_RGR'us (7/28/2011)

    With that distribution I'd really go with PK : SKU, endt

    Then clustered on the date.

    Agreed.

    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 10 posts - 1 through 9 (of 9 total)

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