Modifying a non managed table partition

Question

I ran across a partition that was initially setup (sql 2012) beginning of 2014 to break by month but wasn't updated for 2015. The main issue is that the last partition was setup as Jan 2015 so Feb data is loading into it and I'd like to add future months to the partition and break out the Feb data out of the Jan 2015 partition. My first thought was to load the data to another table and truncate the existing table then drop and recreate the partition as necessary but the table has over a billion records so storage and time are a consideration. What other options would have I have separate the Feb data from the Jan 2015 partition as well as add future months with the least impact to the system?

Here's the existing partitioning function being used.

CREATE PARTITION FUNCTION [YearMonthPartFunction](date) 
    AS RANGE RIGHT 
    FOR VALUES (N'2014-01-01T00:00:00.000', 
                N'2014-02-01T00:00:00.000', 
                N'2014-03-01T00:00:00.000', 
                N'2014-04-01T00:00:00.000', 
                N'2014-05-01T00:00:00.000', 
                N'2014-06-01T00:00:00.000', 
                N'2014-07-01T00:00:00.000', 
                N'2014-08-01T00:00:00.000', 
                N'2014-09-01T00:00:00.000', 
                N'2014-10-01T00:00:00.000', 
                N'2014-11-01T00:00:00.000', 
                N'2014-12-01T00:00:00.000', 
                N'2015-01-01T00:00:00.000')

Answer 1

The best way to do this is:

Let us call your original table Original.

• Create two new tables Jan and Feb) matching the schema of Original. You might want to use the partition management tool for this: https://sqlpartitionmgmt.codeplex.com/. Place these new tables directly on the filegroup you want to end up in. Do NOT place them on a partition scheme.
• Add a check constraint to Jan limiting the data to only the data in January 2015
• Do the same for Feb, but limiting data to February
• Script and drop index on Jan and Feb
• Use INSERT Jan WITH (TABLOCK)... SELECT Original WHERE Month = 'January' to populate Jan. This is bulk logged, and hence, should run at around 40-80MB/sec with minimal impact on the transaction log
• As above, for February.
• Add indexes back on Jan and Feb
• Switch out the old January/February partition from Original to an empty table. Original now has no 2015 records (validate this)
• SPLIT the January partition function of Original on the February/January boundary (and any other months you want to prepare for)
• ALTER TABLE Jan SWITCH TO Original PARTITION x where x is the partition number of January
• ALTER TABLE Feb SWITCH TO Original PARTITION y, where y is the partition number of February
• Profit

This is the fastest way to achieve the result with minimal impact on the disk system and transaction log.

It is rather complicated, but learning Partition Fu tricks like these are key to getting the best out of SQL Server partitioning.