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