Merge a Partition
Published: Sep 17, 2017
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.

Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron