I’m going to briefly dip back into Partitioning for a few posts because this is something I was working on recently and I realized I didn’t have the code to hand on my blog… not that I rely on all code being present, but it’s definitely easier having something to hand than not.
This is actually a small piece of a larger partitioning function, but I decided that it warranted a separate post so here it is…
For this example we’re going to use a copy of my salesOrderHeaderEnlarged table and will presume that we are partitioning our data in a lop-sided manner… by that I mean we have order dates from 2005 to 2011 and I’m going to partition 2011 monthly, but everything older than that will simply be in one large partition.
The problem we’re facing is that December 2010 is still partitioned individually and we need this merging into the larger pre-2011 partition. So how do we achieve this?
Firstly we’ll set up the demo… we’ll create a partition function and scheme, copy our table, partition the table (via adding a clustered index) and then check to make sure that the data is as we expect:
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
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
all to ([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 *
from sys.partitions
where object_id = object_id('partitionSOHE')
go
So now we have our data, how do we merge partitions 1 and 2 in order to move December’s data into our archive?
It’s actually very simple…
alter partition function testPartitionFunction()
merge range ('2010-12-01')
go
select *
from sys.partitions
where object_id = object_id('partitionSOHE')
go
As you can see, the old Partition 1 and 2 have merged (row counts validate that), and all the other partitions have had their IDs adjusted accordingly.
This is a nice and simple command to run… but do be aware that if you’re moving a large amount of data then this can hit your disks quite hard. Something definitely worth performance benchmarking in a test environment first.