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

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  January 14, 2012 at 11:25 am

  #1433727

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

• Paul White

  SSC Guru

  Points: 150468

  More actions

  January 14, 2012 at 11:28 am

  #1433728

  Here are the links to the topics Lynn suggested:

  ALTER PARTITION SCHEME

  ALTER PARTITON FUNCTION

  Paul White
  All articles available on SQL.kiwi
  @SQL_Kiwi

• AlexSQLForums

  SSChampion

  Points: 14277

  More actions

  January 14, 2012 at 1:39 pm

  #1433751

  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