Partitioning Tables

• Andre van der Nest

  Valued Member

  Points: 66

  More actions

  August 14, 2008 at 7:06 am

  #195896

  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