• Then you might want to consider the methods in the attached presentation. It's definitely worth going through the presentation in the "present" mode because it's somewhat animated to explain some of the more serious problems in partitioning WORM tables.

    It also includes full up, documented code to demonstrate it all, which could be modified to your table situation. After the table is partitioned by month, the it's a simple matter of using SPLIT to drop off a month at the temporal beginning of the table. The good part about this is that once you complete an initial backup of each month's filegroup, you only ever have to do a backup on the current month, which saves a ton of time and space not to mention that you'll no longer need to do index maintenance to the larger part of the table.

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