This is a very common thing to need to do and will put together the pieces of my last two posts into one large post in order to achieve the desired outcome.
I’ve seen a good few questions about this on the internet as, although simple when you know how, there are a lot of people who struggle with the best way to achieve it.
For this demo we’re going to start with a table which is partitioned on just one filegroup… we’ll then add a new filegroup and move some of the data to it using MERGE and SPLIT.
Here’s the setup:
use AdventureWorks2012
go
if object_id('partitionSOHE') is not null
drop table partitionSOHE
go
if exists
(
select *
from sys.partition_schemes
where name = 'testPartitionScheme'
)
begin
drop partition scheme testPartitionScheme
end
go
if exists
(
select *
from sys.partition_functions
where name = 'testPartitionFunction'
)
begin
drop partition function testPartitionFunction
end
go
if exists
(
select *
from sys.filegroups
where name = 'Archive'
)
begin
alter database AdventureWorks2012
remove file AW2012Archive
alter database AdventureWorks2012
remove filegroup Archive
end
go
alter database AdventureWorks2012
add filegroup Archive
go
alter database AdventureWorks2012
add file
(
name = 'AW2012Archive',
filename = 'D:\SQLData\AW2012Archive.ndf',
size = 250MB, maxsize = unlimited, filegrowth = 100MB
) to filegroup Archive
go
create partition function testPartitionFunction(datetime)
as range right
for values('2010-12-01', '2011-01-01', '2011-02-01', '2011-03-01', '2011-04-01', '2011-05-01')
go
create partition scheme testPartitionScheme
as partition testPartitionFunction
to ([Archive], [primary], [primary], [primary], [primary], [primary], [primary])
go
if object_id('partitionSOHE') is not null
drop table partitionSOHE
go
select *
into partitionSOHE
from sales.salesOrderHeaderEnlarged
go
create unique clustered index c_partitionSOHE
on dbo.partitionSOHE(salesOrderID, orderDate)
on testPartitionScheme(orderDate)
go
select p.partition_number, p.rows, f.name
from sys.partitions p
join sys.allocation_units a
on p.hobt_id = a.container_id
join sys.filegroups f
on f.data_space_id = a.data_space_id
where object_id = object_id('partitionSOHE')
go
Okay… now what we want to do is to move the December data into the Archive filegroup… this is a simple MERGE:
alter partition function testPartitionFunction()
merge range ('2010-12-01')
go
HOWEVER, let’s say we now want to create a NEW partition for the upcoming month… we MUST remember to ensure that the NEXT partition is created on the PRIMARY and then we can perform a split:
alter partition scheme testPartitionScheme
next used [Primary]
go
alter partition function testPartitionFunction()
split range ('2011-06-01')
go
select p.partition_number, p.rows, f.name
from sys.partitions p
join sys.allocation_units a
on p.hobt_id = a.container_id
join sys.filegroups f
on f.data_space_id = a.data_space_id
where object_id = object_id('partitionSOHE')
go
Simples.
The key things to remember, which I’ve seen mentioned… merging and splitting a partition with data can be VERY expensive an operation, therefore please test first as these are NOT meta data changes, these are physical data moves and therefore can hit disks hard (as the filegroups could easily be on different physical disks).
Also, you cannot switch, merge, or split partitions between databases.