Post reply

Switching Partitions in SQL 2005

  • August 13, 2008 at 2:27 am

    #211138

    Hallo All,

    I'm currently partitioning a huge table into partitions of 3 days per partition. After I created the partitions, I need to switch them out one at a time to separate tables. The table is quite old and each partition contains about 400 000 records. Switching the partitions out goes quite quick, but rebuilding the partition index takes ages.

    I created a loop to loop through the partition table and switch out all but the last 4 partition. Every time I disable the partition index, switch out the data and rebuild the partition index.

    My question is: Do I need to disable the index every time I switch out the data, or can I switch out all the data and then rebuild the partition? My code that I loop through is stated below. I just keep changing the code between (()) to the new table and new range that must be merged.

    WHILE X <= Y

    BEGIN

    ALTER INDEX [IND_MessageID] ON tblMessage DISABLE

    ALTER TABLE tblMessage SWITCH PARTITION 1 TO ((tblMessage_20070101))

    ALTER INDEX [IND_MessageID] ON tblMessage REBUILD WITH (ONLINE = ON)

    ALTER PARTITION FUNCTION [pfQuarter]() MERGE RANGE ((('2007-01-01')))

    SET X = X + 1

    END

  • May 15, 2015 at 3:33 am

    #1798125

    Hi Andre,

    Rebuilding the index for the partitioned table is one of the worst practice.

    Create a staging table with a index similar to the partitioned table. Load the data into staging and then switch the partitions from staging into Partitioned table. By this way you don't need to rebuild the index in big partitioned tables. Comparatively creating a index on staging table with less records is very much better than rebuilding the whole partitioned table.

    Hope this helps. If it helps, I will be very happy..:-D

  • May 15, 2015 at 9:27 pm

    #1798408

    Why are you switched partitions out to rebuild indexes? You do realize you can rebuild indexes on single partitions, correct?

    --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 3 posts - 1 through 2 (of 2 total)

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