Table Partitioning: Exploring Partition Level Operations

http://jpcraighomebuilders.com/wp-json/oembed/1.0/embed?url=http://jpcraighomebuilders.com/portfolio/good-harbor-lake-living/ ALTER INDEX [PK_indexname] ON [tablename] REBUILD PARTITION = ALL WITH (ONLINE= ON);

follow ALTER INDEX [PK_indexname] ON [tablename] REBUILD PARTITION = 1 WITH (ONLINE= ON);

http://mikescarpetconnection.com/2015/04/window-treatment-gallery/  

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')