Sql-server – Moving the data between partitions in a table

database-designpartitioningsql server

There is one table with partition, where partition function and scheme is defined like below:

 CREATE PARTITION FUNCTION DateRangePF (CHAR(8))
  AS RANGE RIGHT FOR VALUES ('20180101','20190101')

 CREATE PARTITION SCHEME DateRangePS 
  AS PARTITION DateRangePF TO (Y2018FG, Y2019FG);

There are lot of data into the partition with boundary value '20190101' and we decided to partition it into 4 parts with following boundary values and associated file groups:

  • '20190101' with file group Y012019FG,
  • '20190401' with file group Y042019FG,
  • '20190701' with file group Y072019FG,
  • '20191001' with file group Y102019FG.

Now, what could be the best strategy to switch the data from older single partition with boundary value '20190101' to these four partitions?

Best Answer

Now, what could be the best strategy to switch the data from older single partition with boundary value '20190101' to these four partitions?

Assuming your partitioned table is aligned, use a similarly partitioned staging table for the partition maintenance. This will avoid the costly data movement and logging when non-empty partitions are split.

Below is an example gleaned from the DDL in your question. Note that your partition scheme DDL is incorrect since the partition function with 2 boundaries creates 3 partitions, with the first being for data prior to year 2018. All 3 partitions must be mapped to a filegroup so I used PRIMARY in this script. Not sure why you chose CHAR(8) instead of DATE for the partitioning column data type since DATE requires only 4 bytes per row compared to 8.

--1) create a staging partition function, scheme, and aligned staging table like the original but with different names:

CREATE PARTITION FUNCTION DateRangePF_Staging (CHAR(8))
    AS RANGE RIGHT FOR VALUES ('20180101','20190101');

CREATE PARTITION SCHEME DateRangePS_Staging
    AS PARTITION DateRangePF_Staging TO ([PRIMARY], [Y2018FG], [Y2019FG]);

 CREATE TABLE YourTable_Staging (
      PartitioningColumn CHAR(8) NOT NULL
    , OtherColumn int NOT NULL
) ON DateRangePS_Staging(PartitioningColumn);

CREATE CLUSTERED INDEX cidx 
    ON YourTable_Staging(PartitioningColumn) 
    ON DateRangePS_Staging(PartitioningColumn);
CREATE NONCLUSTERED INDEX ncidx 
    ON YourTable_Staging(OtherColumn)
    ON DateRangePS_Staging(PartitioningColumn);
GO


--2) `SWITCH` the '20190101' partition into the staging table:

 ALTER TABLE YourTable
    SWITCH PARTITION $PARTITION.DateRangePF('20190101')
    TO YourTable_Staging PARTITION $PARTITION.DateRangePF_Staging('20190101');

--3) split the original partition function to create new boundaries (the split partition is empty after `SWITCH`):

ALTER PARTITION SCHEME DateRangePS
    NEXT USED Y2019FG;
ALTER PARTITION FUNCTION DateRangePF()
    SPLIT RANGE('20190401');
ALTER PARTITION SCHEME DateRangePS
    NEXT USED Y2019FG;
ALTER PARTITION FUNCTION DateRangePF()
    SPLIT RANGE('20190701');
ALTER PARTITION SCHEME DateRangePS
    NEXT USED Y2019FG;
ALTER PARTITION FUNCTION DateRangePF()
    SPLIT RANGE('20191001');
GO

--4) repartition the staging table and indexes using the original partition scheme:    

CREATE CLUSTERED INDEX cidx 
    ON YourTable_Staging(PartitioningColumn) 
    WITH(DROP_EXISTING=ON)    
    ON DateRangePS(PartitioningColumn);
CREATE NONCLUSTERED INDEX ncidx 
    ON YourTable_Staging(OtherColumn)
    WITH(DROP_EXISTING=ON)    
    ON DateRangePS(PartitioningColumn);

ALTER TABLE YourTable_Staging
    SWITCH PARTITION $PARTITION.DateRangePF('20190101')
    TO YourTable PARTITION $PARTITION.DateRangePF('20190101');
ALTER TABLE YourTable_Staging
    SWITCH PARTITION $PARTITION.DateRangePF('20190401')
    TO YourTable PARTITION $PARTITION.DateRangePF('20190401');
ALTER TABLE YourTable_Staging
    SWITCH PARTITION $PARTITION.DateRangePF('20190701')
    TO YourTable PARTITION $PARTITION.DateRangePF('20190701');
ALTER TABLE YourTable_Staging
    SWITCH PARTITION $PARTITION.DateRangePF('20191001')
    TO YourTable PARTITION $PARTITION.DateRangePF('20191001');
GO            

--5) update stats after SWITCH
UPDATE STATISTICS YourTable;    
GO

--6) drop staging objects
DROP TABLE YourTable_Staging;
DROP PARTITION SCHEME DateRangePS_Staging;
DROP PARTITION FUNCTION DateRangePF_Staging;
GO
Related Question