We have a partition function defined as

CREATE PARTITION FUNCTION [PartitionByPeriodFunction](int) 
FOR VALUES (20101228, ... , 20161227) 

where each date is the first date of our fiscal period.

We have a partition scheme defined as

CREATE PARTITION SCHEME [PartitionByPeriodScheme] 
AS PARTITION [PartitionByPeriodFunction] 
TO ([FactsBefore2011], ..., [FactsP201612], [FactsAfter2016])

I want to add more periods to the partition function and more filegroups to the partition scheme - what is the best way to do this?

I have read to drop and recreate the function and scheme - will the dependent objects allow that? I am unsure how to use split since I am not wanting to split 20161227 but rather continue the pattern.


1 Answer 1


You can modify the partition scheme and function to effectively add a new partition.

ALTER PARTITION SCHEME [PartitionByPeriodScheme] NEXT USED [FactsAfter2016_2];

Follow this up with:

ALTER PARTITION FUNCTION [PartitionByPeriodFunction]() SPLIT RANGE 20171231;

This will add a range between 20161227 and 20171231, which will be placed on [FactsAfter2016_2].

As always, try this on a non-production system first.

  • Since the partition function was created with RANGE LEFT, would the split have to apply to the left-hand side (a value earlier than 20101228)? Mar 22, 2016 at 20:13
  • No. LEFT or RIGHT is about where the boundaries sit.
    – Hannah Vernon
    Mar 22, 2016 at 20:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.