Adding a New Filegroup to Existing Partition?

Hi, I recently asked a question about how to create a table partition for a complex data situation that we have.  I think I have figured out the best way to implement this however, I have a question about how to handle new data.

IE:  We have a table partitioned into years... 1995, 1996, 1997, etc... up to current year 2010.

So, I create a bunch of filegroups and create my partition scheme based on the years that currently exist... 1995-2010.

2010 rolls along and I just keep inserting new records into the partitioned table for 2010...

Well, what happens when 2011 comes?  I looked at alter partition function but it only allowed for splitting and merging exisiting partitions into new ones...not just creating a new one entirely.  

So, how does one handle adding a "new" set of ranges to an existing partition... do you have to create a new one entirely and then move the data over?
Roxanne25Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MohammedUCommented:
Check for ALTER PARTITION SCHEMA
Adds a filegroup to a partition scheme or alters the designation of the NEXT USED filegroup for the partition scheme.
http://technet.microsoft.com/en-us/library/ms190347.aspx
0
Roxanne25Author Commented:
Yes I looked at alter partition schema... it only lets you add a partition for an existing file group and as I mentioned the alter partition statement only lets you merge or split filegroups....
0
colinspursCommented:
This should do what you need.  It is taken from here:

http://www.databasejournal.com/features/mssql/article.php/3647561/Data-partitioning-in-SQL-Server-2005---Part-V.htm

Split partition

Execute the following T-SQL statement to split last partition into two partitions.

Alter partition function [Data Partition Range]()
split range(300)
When executed you will get the following error.

Msg 7707, Level 16, State 1, Line 1
The associated partition function 'Data Partition Range' generates
more partitions than there are file groups mentioned in the scheme
'Data Partition Scheme'.
In order to avoid this, we have to add another file group to the database.

Let us execute the below T-SQl statement, which will add a new file group to the database.

Alter database [Data Partition DB5] add FILEGROUP [Data Partition DB5 FG5]
go
alter database [Data Partition DB5]
ADD FILE
       (NAME = 'Data Partition DB5 FG5',
        FILENAME =
         'C:\Data2\FG4\Data Partition DB5 FG5.ndf',
        SIZE = 5MB,
        MAXSIZE=500,
        FILEGROWTH=1 )
TO FILEGROUP [Data Partition DB5 FG5]
Go
Now let us try again to split the partition range by executing the following T-SQL statement.

Use [Data Partition DB5]
go
ALTER PARTITION SCHEME [Data Partition Scheme]
NEXT USED [Data Partition DB5 FG5]
GO
Alter partition function [Data Partition Range]()
split range(500)
Note: We should make SQL Server aware that it should use the next partition when it is splitting. The clause NEXT USED does that.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Roxanne25Author Commented:
Ah awsome!  That is what I needed...then I can split the ranges around to make sure the right years are in the right places... coolies.  Ty. :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.