Adding 2 more years to current partition setup

Question

Post reply

Adding 2 more years to current partition setup

AlexSQLForums

SSChampion

Points: 14277
More actions
January 14, 2012 at 9:00 am

#249077

Hi All
I would like to add 2 more years 2013 and 2014 to my current partition setup.
I've already created files data2013 and data2014 in filegroups fgData2013 and fgData2014
how do i alter scheme and function?
Here is my create scheme and function scripts.
CREATE PARTITION SCHEME [psMonthYearByDate] AS PARTITION [pfMonthYearByDate] TO (
[fgData2010], [fgData2010], [fgData2010], [fgData2010], [fgData2010], [fgData2010], [fgData2010], [fgData2010], [fgData2010], [fgData2010],
[fgData2010], [fgData2010], [fgData2011], [fgData2011], [fgData2011], [fgData2011], [fgData2011], [fgData2011], [fgData2011], [fgData2011],
[fgData2011], [fgData2011], [fgData2011], [fgData2011], [fgData2012], [fgData2012], [fgData2012], [fgData2012], [fgData2012], [fgData2012],
[fgData2012], [fgData2012], [fgData2012], [fgData2012], [fgData2012], [fgData2012])
GO
CREATE PARTITION FUNCTION [pfMonthYearByDate](datetime) AS RANGE LEFT FOR VALUES (
N'2010-02-01T00:00:00.000', N'2010-03-01T00:00:00.000', N'2010-04-01T00:00:00.000', N'2010-05-01T00:00:00.000', N'2010-06-01T00:00:00.000',
N'2010-07-01T00:00:00.000', N'2010-08-01T00:00:00.000', N'2010-09-01T00:00:00.000', N'2010-10-01T00:00:00.000', N'2010-11-01T00:00:00.000',
N'2010-12-01T00:00:00.000', N'2011-01-01T00:00:00.000', N'2011-02-01T00:00:00.000', N'2011-03-01T00:00:00.000', N'2011-04-01T00:00:00.000',
N'2011-05-01T00:00:00.000', N'2011-06-01T00:00:00.000', N'2011-07-01T00:00:00.000', N'2011-08-01T00:00:00.000', N'2011-09-01T00:00:00.000',
N'2011-10-01T00:00:00.000', N'2011-11-01T00:00:00.000', N'2011-12-01T00:00:00.000', N'2012-01-01T00:00:00.000', N'2012-02-01T00:00:00.000',
N'2012-03-01T00:00:00.000', N'2012-04-01T00:00:00.000', N'2012-05-01T00:00:00.000', N'2012-06-01T00:00:00.000', N'2012-07-01T00:00:00.000',
N'2012-08-01T00:00:00.000', N'2012-09-01T00:00:00.000', N'2012-10-01T00:00:00.000', N'2012-11-01T00:00:00.000', N'2012-12-01T00:00:00.000',
N'2013-01-01T00:00:00.000')
GO
Thanks for all your help
Alex S

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Have you looked up ALTER PARTITION SCHEME and ALTER PARTITION FUNCTION in Books Online?

Paul White SSC Guru Points: 150467 More actions · Answer 2

Here are the links to the topics Lynn suggested:

ALTER PARTITION SCHEME

ALTER PARTITON FUNCTION

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

AlexSQLForums SSChampion Points: 14277 More actions · Answer 3

i did and i tried

alter partition scheme psMonthYearByDate next used fgData2013

after adding one i scripted out partition scheme and got this:

CREATE PARTITION SCHEME [psMonthYearByDate] AS PARTITION [pfMonthYearByDate] TO (

[fgData2010], [fgData2010], [fgData2010], [fgData2010], [fgData2010], [fgData2010], [fgData2010], [fgData2010], [fgData2010], [fgData2010],

[fgData2010], [fgData2010], [fgData2011], [fgData2011], [fgData2011], [fgData2011], [fgData2011], [fgData2011], [fgData2011], [fgData2011],

[fgData2011], [fgData2011], [fgData2011], [fgData2011], [fgData2012], [fgData2012], [fgData2012], [fgData2012], [fgData2012], [fgData2012],

[fgData2012], [fgData2012], [fgData2012], [fgData2012], [fgData2012], [fgData2012], [fgData2013], [fgData2012])

GO

so i panicked and decided to post 🙁

is there any way to get rid of [fgData2012] at the end and keep on adding [fgData2013]?

Alex S