Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The DBA Who Came In From The Cold

I’m Andrew Pruski and I am a SQL Server DBA with 5 years experience in database development and administration.

The online SQL Server community has helped me out immensely throughout my career, whether from providing reference material in blog posts, or answering my (sometimes obscure) questions on forums. So, to try and say thank you, I would like to contribute my own experiences in the hope that they could benefit someone out there.

So here’s my general ramblings and thoughts about working as a SQL Server DBA.

You can find me on twitter @DBAFromTheCold

If you have any feedback on my blog please send me an email to dbafromthecold@gmail.com.

Partitioning Basics – Part 3 – Switching Data

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:-

Partitions4

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:-

Partitions6

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:-

Partitions Final

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.


Comments

Leave a comment on the original post [dbafromthecold.wordpress.com, opens in a new window]

Loading comments...