Table Partitioning Basics(2)

  • Article

In my last blog I looked at SPLIT and MERGE operations on partition functions. SPLIT and MERGE are nice functions but I use them with care. A merge is not the best performing function there is…. And that is completely logical when you think about everything that is happening during execution:

  1. Data needs to be transferred from one partition to another.
  2. When the new partition is on another filegroup data needs to be transferred physically.
  3. All storage aligned indexes(and all indexes need to be storage aligned!) must be updated.

All very time-consuming tasks. Not things you want to do every day. A split is usually an easy operation because you usually add an partition at the end of the range. If you split existing data the same rules apply as to a merge.

 
 

So what is a good idea then to move data around in an partitioned table? When you need to move complete partitions the SWITCH operator is a good choice.

 
 

ALTER TABLE Test

SWITCH PARTITION 1

TO StagingTable

 
 

This statement moves all data in partition 1 to the staging table StageTest. The staging table needs to have exactly the same schema as the partitioned table. It also needs to have the same clustered and non-clustered indexes and the same constraints. It also needs to be empty. This is a bit of a pain but if you don't do this you'll get errors:

 
 

Msg 4947, Level 16, State 1, Line 1

ALTER TABLE SWITCH statement failed. There is no identical index in source table Test.dbo.StagingTable' for the index 'IX_Date' in target table 'Test.dbo.PartitionSample'.

 
 

Msg 4982, Level 16, State 1, Line 1

ALTER TABLE SWITCH statement failed. Check constraints of source table 'Test.dbo.StagingTable' allow values that are not allowed by range defined by partition 4 on target table 'Test.dbo.PartitionSample'.

 
 

Msg 4905, Level 16, State 1, Line 1

ALTER TABLE SWITCH statement failed. The target table 'Test.dbo.StagingTable' must be empty.

 
 

Switching out takes seconds instead of hours so the choice is easy.

 
 

You can switch in data as well with the following statement:

 
 

ALTER TABLE StagingTable

SWITCH TO Test PARTITION 4

TO Test

 
 

This is also very fast, especially when the partition is empty.

 
 

So concluding: when you want to merge to big partitions you switch out the partition to merge to a staging table. Then you remove the boundary point with the ALTER PARTITION FUNCTION…MERGE command. After that you switch the data back in to the partitioned table.

 
 

This week I'm going to implement a complicated sliding window scenario at one of our customers. The scenario includes a partition function with daily partitions for the current week, weekly partitions for the last four weeks and monthly partitions for older data. I'll blog about that later this week.