Quick Guide to Partition Switching
Published: 25th April 2015
Partition Switching is an incredibly fast way to insert or delete data from a partitioned table and works very well in data warehousing environments, for example in which large overnight loads take place.

Let’s say we have a partitioned table holding data split by data and we want to delete a date range on a nightly basis (rolling date range). This can take time because normally we would have to run a delete statement which would involve indexes and a lot of disk and transaction log activity. During this time the table would also be locked. So how do we get around this?

Well we simply need to partition the table accordingly and then switch out the data. This can take less than a second to remove any amount of data from your table.

In simple terms how it works is as follows:

Let’s say you have a partitioned table with 10 partitions and you want to delete all the data from partition 5... well in the traditional way you would likely have to take an exclusive table lock and hold it until the delete had finished which, depending on how big the dataset is, could be a long while… so what about partitioning?

What you do is you create an empty table that mimics your partitioned table and then you literally swap the empty table for the partition full of data you want to remove.

In the background SQL Server has a 1 partitioned table made up of many little tables stitched together so all it does is change the meta data so that before the swap partition 4 points to partition 5 and 5 to 6… but after the swap partition 4 points to your empty table, the empty table to partition 6. Therefore it’s a meta data change and so incredibly quick.

Hopefully that makes sense as it’s hard to explain like this and I’m not good at drawing pictures!!!

We’ll do this in an example so that you can see how to make it work yourself…

use AdventureWorks2012
go

if object_id('testPartition') is not null drop table testPartition
go

if exists
(
  
select *
  
from sys.partition_schemes
  
where name = 'testPartitionScheme'
)
begin
   drop
partition scheme testPartitionScheme
end

if
exists
(
  
select *
  
from sys.partition_functions
  
where name = 'testPartitionFunction'
)
begin
   drop
partition function testPartitionFunction
end

create
partition function testPartitionFunction(date)
  
as range left
  
for values('2008-01-01', '2008-02-01', '2008-03-01', '2008-04-01')
go

create partition scheme testPartitionScheme
  
as partition testPartitionFunction
  
all to ([primary])
go

create table testPartition
(
  
id int identity,
  
orderDate date,
  
accountNumber varchar(20),
  
purchaseOrderNumber varchar(20),
  
totalDue decimal(19, 8),
  
constraint pk_testPartition primary key clustered(id, orderDate)
)
on testPartitionScheme(orderDate)
go

insert into testPartition
select orderDate, AccountNumber, PurchaseOrderNumber, TotalDue
from sales.SalesOrderHeader
where orderDate between '2007-12-01' and '2008-05-01'
go

/*
   delete all data from partition 2, which is between '2008-01-01' and '2008-02-01'
*/

-- Create blank table...  must have the same indexing

if object_id('testDeletePartition') is not null drop table testDeletePartition
go

create table testDeletePartition
(
  
id int identity,
  
orderDate date,
  
accountNumber varchar(20),
  
purchaseOrderNumber varchar(20),
  
totalDue decimal(19, 8),
  
constraint pk_testDeletePartition primary key clustered(id, orderDate)
)

-- Switch this empty table into partition 2

alter table testPartition
switch partition 2
to testDeletePartition

-- Now all the data for partition 2 will be in testDeletePartition
-- We can therefore delete this quickly...  the entire operation taking a couple of seconds

truncate table testDeletePartition


Hopefully that makes more sense.

Note that the procedure is the same if you want to switch data in rather than delete data… likewise if you want to do a large update to data… you can do this offline in another table with all the blocking you like, and then swap the updated data into the partitioned table.
Comments:
NB: Comments will only appear once they have been moderated.