Post reply

Partitioning and Indexes

  • July 10, 2015 at 6:24 am

    #305681

    Hi

    I have a table that holds in excess of 700 million records and approx. 40 columns, the table is partitioned by Date. The primary key for the table is the Surrogate Key which is unique and auto increments by 1.

    I am looking at the best way to design the table with the following options:

    Option 1: Partitioned Table by Date, (No Indexes on table)

    Option 2 : Partitioned Table By Date, (Clustered Index on Date)

    Option 3: Partitioned Table By Date (Non Clustered Index on Date)

    The answer I am really looking for is should the table be indexed at all, typically the users may query by a particular date.

    Many Thanks

  • July 10, 2015 at 9:34 am

    #1811658

    Yes, the table should still be indexed...otherwise a table scan will be performed for all queries and you won't see any performance benefits from your partitioning strategy.

    In my opinion you should go with option 3 and create a partition-aligned non-clustered index on the Date field.

  • July 10, 2015 at 12:15 pm

    #1811721

    Why are you planning to partition? What's the purpose? What's the goal?

    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 10, 2015 at 12:17 pm

    #1811722

    I wouldn't (and haven't) used any of those 3 methods. I'd (and have) do the clustered index by Date (the partitioning column) and the PK. It's going to happen anyway because the PK is unique. And make sure it's a unique clustered index.

    If the data in old months is 100% static (think "audit table" or "closed invoice detail table"), then also consider making the DATE partitions live on 1 filegroup/file per month and make them Read-Only so that you don't have to worry about backing them up or doing index maintenance any more. It's been a real timesaver for me and will make "Piece-Meal" restores a whole lot easier if {knocking on wood} such a thing is ever needed.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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