Home Forums SQL Server 2008 SQL Server 2008 Administration Hash Partitioning RE: Hash Partitioning

  • May 19, 2012 at 10:06 am

    #1490133

    opc.three (5/19/2012)

    GilaMonster (5/19/2012)

    You are highly unlikely to achieve anything other than adding complexity and wasting time. Partitioning is not a performance optimisation (mostly), it's for ease of maintenance, loading and deleting rows.

    Partitioning a table so that is it useful requires a careful choice of partitioning column based on the data and the queries, and a semi-random hash is highly unlikely to be useful.

    Honestly, don't go this route.

    What about for tables with no natural partitioning key and a low cardinality on all candidates?

    I agree with Gail. It won't help for performance and it won't help with maintenance because the hash partitioning would mean that new rows added have just as much likelyhood of being added to one partition as another. It's no better than partitioning on something like NEWID(). IMHO, hash partitioning isn't just a waste of time, it's a move in the wrong direction. You're adding complexity to make things worse. It's kind of like using NEWID() as the clustered index of a table except it's worse.

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