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.
The following script will createSince the historicalfirst partition is on the wrong filegroup (PRIMARY), assuming you have createdfirst move it to the neededproper 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(2013120020140100);
If you plan to create and load additional historical monthsThen, either create the partitions ahead of time or create and load the needed historical partitions in reverse chronological order.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.