Table Partitioning: Adding\Removing partitions using SPLIT, MERGE

In this tip we’ll see one of the advantage of partitioning a very large table, which is the ability to add or remove partitions instantaneously to help you with sliding window scenarios.

Lets first create partition function and partition scheme. Here i m using monthly partition scheme.

CREATE PARTITION FUNCTION [PF_MONTHLY](date) AS RANGE LEFT FOR VALUES 
   ('2016-01-31', '2016-02-29', N'2016-03-31', N'2016-04-30', N'2016-05-31')

CREATE PARTITION SCHEME [PS_MONTHLY] AS PARTITION [PF_MONTHLY] 
   ALL TO ([XYZ_FILE_GROUP])

Now lets add new partition for next month using partition split.

ALTER PARTITION SCHEME ps_monthly NEXT used XYZ_FILE_GROUP

ALTER PARTITION FUNCTION pf_function() SPLIT RANGE ('2016-06-30')