Adding new partitions to an existing, partitioned table RRS feed

  • Question

  • Hi,

    I have a problem, which I am sure, in part, has been answered in fragmented forums. I only have rudimentary skills with SQL Server, so would appreciate confirmation from a professional...! I will try and provide as much info as I can:

    I have a sales table (1000 Million rows) which is already partitioned into weekly chunks. The table is broken into Filegroups associated with each week e.g. Sales_2012_03_04, Sales_2012_03_11 etc. Each filegroup holds 2 files e.g. Sales_2012_03_04_f01 and Sales_2012_03_04_f02.

    The partition scheme associated with these files follows:


    PARTITION SCHEME [PartitionScheme_Sales_DateID] AS PARTITION [fnPartition_Sales_DateID] TO ([Sales_2012_02_19], [Sales_2012_02_26], [Sales_2012_03_04],................, [Sales_2014_02_16]) 

    The partition function associated with above is as follows:


    PARTITION FUNCTION [fnPartition_Sales_DateID](int) AS RANGE RIGHT FOR VALUES (20120226, 20120304,

    ..., 20140216) 

    At the moment (21st Mar 2014) , the final partition [Sales_2014_02_16] continues to grow. I need (presumably) to add new files, filegroups, amend the partition function and partition scheme, and then run something to allow the current data in this last partition to span across the newly created partitions.

    I would like to apply a further year's worth of partitions, so would need to generate a script to do this. As well as the technicalities of how to this, I would benefit from knowing roughly how long the operation will take. I don't know for example if the process requires all data to spread over all partitions, or whether we can restrict it to only look at 2014_0216 partitions and beyond.

    I can test the process on a less-critical database first, before executing on live, If anyone could provide a script, or at very least a systematic approach, I would be most grateful. Many thanks, Ken

    • Moved by Kalman Toth Friday, March 21, 2014 3:54 PM Better fit
    Friday, March 21, 2014 3:03 PM


All replies