Moving the data between partitions in a table

Question

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?

Answer 1

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