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.

3 Replies to “SQL Server Partitioning Data Movement”

    1. My example is correct as far as I can tell. If you have three partitions, the first with no rows and the second with rows. Then if you merge the first boundary point, because there are no rows in the first partition it will choose to remove the first.

      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]
      GO
      ALTER DATABASE [MyTest] ADD FILE
      (NAME = N’One_File01′,FILENAME = N’C:\SQL\MSSQL11.MSSQLSERVER\MSSQL\DATA\One_File01.ndf’)
      TO FILEGROUP [One]
      GO
      ALTER DATABASE [MyTest] ADD FILE
      (NAME = N’Two_File01′,FILENAME = N’C:\SQL\MSSQL11.MSSQLSERVER\MSSQL\DATA\Two_File01.ndf’)
      TO FILEGROUP [Two]
      GO
      ALTER DATABASE [MyTest] ADD FILE
      (NAME = N’Three_File01′,FILENAME = N’C:\SQL\MSSQL11.MSSQLSERVER\MSSQL\DATA\Three_File01.ndf’)
      TO FILEGROUP [Three]
      GO
      GO
      USE [MyTest]
      CREATE PARTITION FUNCTION pfR(INT)
      AS RANGE RIGHT FOR VALUES (100,200);
      CREATE PARTITION SCHEME psR AS PARTITION pfR TO
      ([One],[Two],[Three]);
      CREATE TABLE R ([ID] INT) ON psR([ID]);

      INSERT INTO R VALUES (101)
      INSERT INTO R VALUES (201)
      GO
      INSERT INTO R
      SELECT * FROM R
      GO 20
      GO
      SELECT [size],[name] FROM sys.master_files WHERE [file_id] > 2 AND [database_id] = DB_ID(‘MyTest’)
      GO
      ALTER PARTITION FUNCTION pfR() MERGE RANGE(100)
      GO
      SELECT [size],[name] FROM sys.master_files WHERE [file_id] > 2 AND [database_id] = DB_ID(‘MyTest’)
      GO
      DBCC SHRINKFILE (N’One_File01′ , EMPTYFILE)
      GO
      ALTER DATABASE [MyTest] REMOVE FILE [One_File01]
      GO
      SELECT [size],[name] FROM sys.master_files WHERE [file_id] > 2 AND [database_id] = DB_ID(‘MyTest’)
      GO
      USE master
      GO
      DROP DATABASE [MyTest]

Leave a Reply