SQL Server Partitioning Data Movement

Introduction

This is a continuation to the partitioning overview post to help explain how data movement is performed using Alter Partition with Merge and Split Commands.

Getting Started

In order to move data in or out of a partitioned object the part of the object that needs to be moved must reside on the same file group. In the first post all the partitions were placed on the primary file group. Now if the middle partition P2 was removed using the MERGE syntax this would result in P3 renumbering itself to P2. So to make things easier to follow the next example will create file groups called One,Two,Three and Four which will correspond to the initial partition locations.

The code below is for a RANGE RIGHT partition, but included is the code for a RANGE LEFT.

CREATE DATABASE [MyTest]
GO
USE [master]
GO
ALTER DATABASE [MyTest] ADD FILEGROUP [One]
ALTER DATABASE [MyTest] ADD FILEGROUP [Two]
ALTER DATABASE [MyTest] ADD FILEGROUP [Three]
ALTER DATABASE [MyTest] ADD FILEGROUP [Four]
GO
ALTER DATABASE [MyTest] ADD FILE
(NAME = N'One_File01',FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\One_File01.ndf')
TO FILEGROUP [One]
GO
ALTER DATABASE [MyTest] ADD FILE
(NAME = N'Two_File01',FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Two_File01.ndf')
TO FILEGROUP [Two]
GO
ALTER DATABASE [MyTest] ADD FILE
(NAME = N'Three_File01',FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Three_File01.ndf')
TO FILEGROUP [Three]
GO
ALTER DATABASE [MyTest] ADD FILE
(NAME = N'Four_File01',FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Four_File01.ndf')
TO FILEGROUP [Four]
GO
USE [MyTest]
CREATE PARTITION FUNCTION pfR(INT)
AS RANGE RIGHT FOR VALUES (100,200,300);
CREATE PARTITION SCHEME psR AS PARTITION pfR TO
([One],[Two],[Three],[Four]);
CREATE TABLE R ([ID] INT) ON psR([ID]);
INSERT INTO R VALUES (NULL)
INSERT INTO R VALUES (-1)
INSERT INTO R VALUES (0)
INSERT INTO R VALUES (1)
INSERT INTO R VALUES (100)
INSERT INTO R VALUES (101)
INSERT INTO R VALUES (200)
INSERT INTO R VALUES (201)
INSERT INTO R VALUES (300)
INSERT INTO R VALUES (301)
INSERT INTO R VALUES (1001)

/*
CREATE PARTITION FUNCTION pfL(INT) AS RANGE LEFT FOR VALUES (100,200,300);
CREATE PARTITION SCHEME psL AS PARTITION pfL TO
([One],[Two],[Three],[Four]);
CREATE TABLE L ([ID] INT) ON psL([ID]);
INSERT INTO L VALUES (NULL)
INSERT INTO L VALUES (-1)
INSERT INTO L VALUES (0)
INSERT INTO L VALUES (1)
INSERT INTO L VALUES (100)
INSERT INTO L VALUES (101)
INSERT INTO L VALUES (200)
INSERT INTO L VALUES (201)
INSERT INTO L VALUES (300)
INSERT INTO L VALUES (301)
INSERT INTO L VALUES (1001)

--DROP TABLE L
--DROP PARTITION SCHEME psL
--DROP PARTITION FUNCTION pfL
--DROP TABLE R
--DROP PARTITION SCHEME psR
--DROP PARTITION FUNCTION pfR
*/

You can check where each row is using the Detailed Table and Index Breakdown script

Merge

Using the example below of a RANGE RIGHT partition what would happen if the secondary boundary was removed.

RightPartition

In order to remove the secondary boundary this command is needed:

ALTER PARTITION FUNCTION pfR() MERGE RANGE(200)

The resultant change is that the partition to the right of the boundary shown below is removed and the data is moved to partition 2. The example has rows in both partitions, but this would also be true if both P2 and P3 had been empty, except no data would have been moved.

RightPartitionMergeBoundary200

But what if instead the values 100 and 101 had not been inserted and P2 was empty?

I need to add a note here to say that the following is true from SQL 2012 with regards to empty partitions, as was highlighted this is not true for versions prior. For previous versions see the following post: http://www.brentozar.com/archive/2013/01/best-practices-table-partitioning-merging-boundary-points/

 

RightPartitionEmptyP2

In this case data is not moved from P3 but instead P2 is removed.

RightPartitionEmptyP2MergeBoundary200

If the merge was on a RANGE LEFT example then it will remove the left partition. The easiest way to think about this is that the partition that satisfies the boundary condition will be removed, unless there are no rows in the other partition that does not satisfy the boundary condition. Hopefully that is clear if you imagine the two sides of your boundary.

If nulls are allowed on the partition column then they will be stored in P1, which is important when considering range left or right. A partition merge on a range right that has nulls in P1 will remove P2, where as a range left will remove P1.

Split

If you want to add partitions you must first specify the next filegroup to use. In order to put back the partition removed from the split above the following code is needed:

Right-

ALTER PARTITION SCHEME psR NEXT USED [Three]
GO
ALTER PARTITION FUNCTION pfR() SPLIT RANGE(200)

Left-

ALTER PARTITION SCHEME psL NEXT USED [Two]
GO
ALTER PARTITION FUNCTION pfL() SPLIT RANGE(200)

Switch

Switching is perhaps the most useful of the movement commands in that you can move data incredibly easily.

If the contents of P2 needed to be moved out we would first need to create a destination table before switching P2. The destination table does not need to be partitioned if we are moving only one source partition. In some cases it maybe easier to create an exact copy of your source structure as then you can move partitions specifying each partition number as needed.

CREATE TABLE R2 ([ID] INT) ON [Two];
GO
ALTER TABLE [dbo].[R] SWITCH PARTITION 2 TO R2

The two rows from P2 of the source table are now present in the destination table and P2 of source is showing as zero rows.

Now to put the rows back…

ALTER TABLE [dbo].[R2] SWITCH TO [dbo].[R] PARTITION 2

ALTER TABLE SWITCH statement failed. Check constraints of source table ‘dbo.R2’ allow values that are not allowed by range defined by partition 2 on target table ‘dbo.R’.

In order for this to work we need to confirm that the data in the R2 table will fit in to P2, which means a constraint. Now P2 should hold values that are greater than or equal to 100, but less than 200. (Nulls are held in P1)

ALTER TABLE [dbo].[R2] ADD CONSTRAINT [PleaseBelieveMe]
CHECK ([ID]>=N'100' AND [ID]<N'200' AND [ID] IS NOT NULL)

The switch will now work correctly and the rows are returned. This process becomes slightly more complicated with indexes but the theory is the same and is explained well in Special Guidelines for Partitioned Indexes.

The only issue that I have had with SWITCH is when conflict detection is enabled on a peer to peer replication, which is logged here on connect.

SQL Server Partitioning Overview

Introduction

This is a guide to help explain partitioning setup; which although introduced way back in SQL 2005 is still an area which throws up a lot of questions.

Requirements

First off you will need Enterprise SQL Server in your production environment, or alternatively you can explore this feature in SQL Developer edition.

Getting Started

In order to partition there are two objects that you will first need to create these are a PARTITION FUNCTION and a PARTITION SCHEME.

Function

The partition function is the first building block that you need and requires that you specify boundaries.

CREATE PARTITION FUNCTION pfL(INT)
AS RANGE LEFT
FOR VALUES (100,200,300);

The syntax is simple and shows that in the above case the function expects an integer and will create boundaries based on a RANGE LEFT strategy. The example shows three boundaries which will mean that there are four partitions. Number of boundaries + 1.

RANGE LEFT means that the boundary points are <= less than or equal. In the example below the first boundary is 100 which means all values less than or equal to 100 will go in to the first partition, those above 100 and less than the next boundary will fit in to partition two (P2) and so on.

The below image shows this and with example values sorted.

LeftPartition

Now obviously having shown RANGE LEFT, here is a RANGE RIGHT example. Note that now the boundaries points are => greater than or equal to. So the value 100 for example is now in the second partition.

RightPartition

Scheme

Before we can apply the partition, a partition scheme needs to be deployed which will identify the file groups to use.

CREATE PARTITION SCHEME psL
AS PARTITION pfL TO ([PRIMARY],[PRIMARY],[PRIMARY],[PRIMARY]);

The table below specifies that it will use the partition scheme and will be partitioned on the ID column and to make this example simple all the data is stored on the Primary file group.

CREATE TABLE L ([ID] INT) ON psL([ID])
INSERT INTO L VALUES (NULL)
INSERT INTO L VALUES (-1)
INSERT INTO L VALUES (0)
INSERT INTO L VALUES (1)
INSERT INTO L VALUES (100)
INSERT INTO L VALUES (101)
INSERT INTO L VALUES (200)
INSERT INTO L VALUES (201)
INSERT INTO L VALUES (300)
INSERT INTO L VALUES (301)
INSERT INTO L VALUES (1001)

You can check where each row is using the Detailed Table and Index Breakdown script