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