• Michael Valentine Jones (6/21/2012)


    In conjunction with this, they could have the web application query the table using the snapshot isolation level (or set the database to read committed snapshot) so that the web app will still be able to see the data that was committed before the update transaction started.

    That would eliminate even momentary blocking, and be much simple to implement than a partitioned table.

    I haven't read the rest of the posts on this thread yet but I agree with the above. Partitioning isn't as easy as some would have you believe. For example, if you have any unique indexes on the table, the partitioning key will automatically be added which makes them {drum roll please} non-unique based on the original unique column(s). It also makes FKs to the table damned near impossible for the same reason unless the only UNIQUE index is also the partitioning column itself.

    If the table has no foreign keys and the only UNIQUE index is based on an IDENTITY column, then partitioning gets a whole lot easier but I still like the idea of Michaels suggestion for this particular problem much better.

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