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)
- From SSMS, get the properties of Database and move to Files tab.
- Click Add button on the bottom right to add a new line.
- Put a new file name (e.g. FN_DW_Archieve_2013_02)
- Put a new Logical Name (e.g. LN_DW_Archieve_2013_02)
- Create a new Filegroup by selecting in the dropdown under filegroup cell.
(e.g. FG_DW_Archieve_2013_02) - 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.
Best Answer
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: