Best way to increase no of partition in SQL Server Table

Question

I am maintaining a database with a big big table, which portioned for each month. There is already tens of partition there but its till January 2013, So its time to create new partitions.

Can anyone please advise, what's the best way to create new number of partitions.

I am doing the following step (Yet not did)

1. From SSMS, get the properties of Database and move to Files tab.
2. Click Add button on the bottom right to add a new line.
3. Put a new file name (e.g. FN_DW_Archieve_2013_02)
4. Put a new Logical Name (e.g. LN_DW_Archieve_2013_02)
5. Create a new Filegroup by selecting in the dropdown under filegroup cell. (e.g. FG_DW_Archieve_2013_02)
6. Hit Ok

This will create a new file group in my database.

Now the biggest problem which make me worry is to modify the Partition Function and Partition Scheme. Which expert advise, can I drop and recreate them again, does it affect my existing data. Or can I alter them with the new partition in it.

Thanks in advance.

Answer 1

It sounds like what you want to do is to create a new boundary for your existing partitioning implementation, and this can be one through ALTER PARTITION FUNCTION ... SPLIT RANGE ....

You will also need to run prior to this an ALTER PARTITION SCHEME ... NEXT USED ... to indicate the next filegroup that will hold the new partition (there are a few caveats to this, as quoted below). Here is a quote from BOL regarding this operation:

A filegroup must exist online and be marked by the partition scheme that uses the partition function as NEXT USED to hold the new partition. Filegroups are allocated to partitions in a CREATE PARTITION SCHEME statement. If a CREATE PARTITION SCHEME statement allocates more filegroups than necessary (fewer partitions are created in the CREATE PARTITION FUNCTION statement than filegroups to hold them), then there are unassigned filegroups, and one of them is marked NEXT USED by the partition scheme. This filegroup will hold the new partition. If there are no filegroups marked NEXT USED by the partition scheme, you must use ALTER PARTITION SCHEME to either add a filegroup, or designate an existing one, to hold the new partition.