This post follows on from Partitioning Basics – Part 2
In this final part, I want to go through how partitions can be used to archive data from a table. Firstly a table to archive the data from the primary table needs to be created:-
CREATE TABLE dbo.[DemoPartitionedTable_Archive] (DemoID INT IDENTITY(1,1), SomeData SYSNAME, CaptureDate DATE, CONSTRAINT [PK_DemoPartitionedTable_Archive] PRIMARY KEY CLUSTERED (DemoID ASC, CaptureDate ASC) ) ON DEMO;
I haven’t created this table on the partition scheme but it could be done because, according to Microsoft:-
“When a table and its indexes are aligned, then SQL Server can move partitions in and out of partitioned tables more effectively, because all related data and indexes are divided with the same algorithm.”
Reference:- Partitioned Tables and Indexes in SQL Server 2005
So if the archive table was on the partition scheme it would be “Aligned”. This means that moving data in and out would be more efficient. The only reason I haven’t done this here is because this is a basic demo.
Before the switch is performed the data in the partitions needs to be checked:-
SELECT t.name AS TableName, i.name AS IndexName, p.partition_number, r.value AS BoundaryValue, p.rows FROM sys.tables AS t INNER JOIN sys.indexes AS i ON t.object_id = i.object_id INNER JOIN sys.partitions AS p ON i.object_id = p.object_id AND i.index_id = p.index_id INNER JOIN sys.partition_schemes AS s ON i.data_space_id = s.data_space_id INNER JOIN sys.partition_functions AS f ON s.function_id = f.function_id LEFT OUTER JOIN sys.partition_range_values AS r ON f.function_id = r.function_id AND r.boundary_id = p.partition_number WHERE t.name = 'DemoPartitionedTable' AND i.type <= 1 ORDER BY p.partition_number;
Here’s the data in the partitions:-
In order to switch the data from partition 1 in the primary table to the archive table, the following script needs to be run:-
ALTER TABLE dbo.[DemoPartitionedTable] SWITCH PARTITION 1 TO dbo.[DemoPartitionedTable_Archive]; GO
Which makes the partitions look like this:-
Partition 1 has 0 rows and could be merged. The following script will merge the partition:-
ALTER PARTITION FUNCTION DemoPartitionFunction() MERGE RANGE ('2014-05-29'); GO
So now the partitions are:-
The partition has had its data switched out to the archive table and then was merged into the above partition. Using the scripts in these three post will allow you to effectively manage partitioning in SQL Server.
I hope this series of posts has been helpful as an introduction into partitioning. Please let me know if you have any comments or questions.
4 thoughts on “Partitioning Basics – Part 3 – Switching Data”
Your conversation is so beautiful, I ask why in Archive you do IDENTITY, because we need to copy/move data as is, and DemoID is already full with data, so why IDENTITY in Archive (sorry for my bad english). thank you very much
Good spot! The IDENTITY property is set on the Archive table purely to match the Live table however it’s not really needed. It can be removed from the CREATE TABLE statement if you don’t want it there.
Excelentes documentos que nos sirven para comprender aun mas como particionar las tablas y darles mantenimiento una vez paticionadas, muchas gracias
Excellent documents that help us to understand even more how to partition the tables and maintain them once patitioned, thank you very much