1

I have a table called dbo.Audit. I initially had file groups like fgAudit2012, fgAudit2013 upto fgAudit2030 for years 2012 to 2030. The name of my partition function is pfMonthly and name of partition scheme is psMonthly. Each file groups (per year) has 12 partitions (per month) as shown in screenshot below. Name of the column used for partition is ActionDateID of datatype INT and will hold datekey values.

I decided to add three more file groups to the table. One for year 2011 and one empty file group for all years earlier than year 2011 and one for all years later than 2030.

The first thing I did was create a file group called fgAudit2011 and add file called fileAudit2011 to it. And I added this file group at the front of the partition by using

ALTER PARTITION SCHEME psMonthly NEXT USED fgAudit2011

and

ALTER PARTITION FUNCTION pfMonthly() SPLIT RANGE (20110101)

I repeated this twelve times for twelve months. The result is as shown in screenshot below.

Now the partition at the very bottom is pointing to the file group ftAudit2012 which is wrong. How can I add two empty partitions at the two ends? File group fgAuditbefore2011 should be in the front and fgAuditAfter2030 should be at the last. How can I do this without dropping and recreating table or removing and recreating partition functions and partition schemes?

enter image description here

Merin Nakarmi
  • 3,148
  • 3
  • 35
  • 42

0 Answers0