SQL Server Partitioning – How to Split Partition to Load Historical Data

partitioningsql serversql-server-2016

We have a table partitioned using the following partition schema and function

CREATE PARTITION FUNCTION [pf_monthly_dateid](int) AS RANGE LEFT FOR VALUES (20140100, 20140200, 20140300, 20140400, 20140500, 20140600, 20140700, 20140800, 20140900, 20141000, 20141100, 20141200, 20150100, 20150200, 20150300, 20150400, 20150500, 20150600, 20150700, 20150800, 20150900, 20151000, 20151100, 20151200, 20160100, 20160200, 20160300, 20160400, 20160500, 20160600, 20160700, 20160800, 20160900, 20161000, 20161100, 20161200, 20170100, 20170200, 20170300, 20170400, 20170500, 20170600, 20170700, 20170800, 20170900, 20171000, 20171100, 20171200, 20180100, 20180200, 20180300, 20180400, 20180500, 20180600, 20180700, 20180800, 20180900, 20181000, 20181100, 20181200)
GO

CREATE PARTITION SCHEME [ps_monthly_dateid] AS PARTITION [pf_monthly_dateid] TO ([PRIMARY], [201401], [201402], [201403], [201404], [201405], [201406], [201407], [201408], [201409], [201410], [201411], [201412], [201501], [201502], [201503], [201504], [201505], [201506], [201507], [201508], [201509], [201510], [201511], [201512], [201601], [201602], [201603], [201604], [201605], [201606], [201607], [201608], [201609], [201610], [201611], [201612], [201701], [201702], [201703], [201704], [201705], [201706], [201707], [201708], [201709], [201710], [201711], [201712], [201801], [201802], [201803], [201804], [201805], [201806], [201807], [201808], [201809], [201810], [201811], [201812])
GO

We now need to load some historical data into the table for 2013 so I want to alter the function and schema to add monthly partitions for this. But I can't work out how to do this using SPLIT? Every example and tutorial I've looked at shows how to add new partitions onto the end of a range, not split one in the middle.

Any suggestions please?

Best Answer

You mention splitting at the middle, but your requirement is adding a boundary to the beginning (before the first existing boundary). Consequently, no costly data movement will be needed as long as no existing values are less than or equal to 20140100. I'll assume that is the case here.

Since the first partition is on the wrong filegroup (PRIMARY), first move it to the proper monthly filegroup:

--move first partition to proper filegroup
ALTER PARTITION FUNCTION [pf_monthly_dateid]()
    MERGE RANGE(20140100);
ALTER PARTITION SCHEME [ps_monthly_dateid] 
    NEXT USED [201312];
ALTER PARTITION FUNCTION [pf_monthly_dateid]()
    SPLIT RANGE(20140100);

Then, create the needed historical partitions before loading data:

ALTER PARTITION SCHEME [ps_monthly_dateid] 
    NEXT USED [201311];
ALTER PARTITION FUNCTION [pf_monthly_dateid]()
    SPLIT RANGE(20131200);
ALTER PARTITION SCHEME [ps_monthly_dateid] 
    NEXT USED [201310];
ALTER PARTITION FUNCTION [pf_monthly_dateid]()
    SPLIT RANGE(20131100);
ALTER PARTITION SCHEME [ps_monthly_dateid] 
    NEXT USED [201309];
ALTER PARTITION FUNCTION [pf_monthly_dateid]()
    SPLIT RANGE(20131000);
...

The general best practice is to plan such than only empty partitions are split. Data movement performed during SPLIT and MERGE to changed boundaries requires about 4 times logging as normal DML. So also consider creating an empty partition on the proper filegroup (even if no files) in anticipation of loading prior data.

Related Question