SQL Server 2012 – Why Does Partition Range Right Skip Deletes/Inserts?

partitioningsql serversql-server-2012

I have two example Queries (shown below). Both (drop and) create a database and a partitioned table in it. The table has several partitions, with data in each of them (including the last, unbounded, one). The partitioned data goes from 1 to 3996.

Then each script adds a new partition. The new partition starts at 4000.

In the first script the transaction log shows that every record in the last partition (Partition 4) is deleted and re-inserted.

The second script shows that no activity for the rows took place (no deletes and inserts).

The ONLY difference between the two scripts is the RANGE LEFT vs RANGE RIGHT on the Partition Function. RANGE RIGHT causes no deletes or inserts, RANGE LEFT causes all the rows in the last partition to be deleted and reinserted.

I thought RANGE LEFT or RANGE RIGHT just controlled if the border value went with the left or right partition. But it clearly does something else too.

Is there more to RANGE LEFT and RANGE RIGHT that I don't understand?

Also, I like the idea of adding partitions without impacting my system. And I am willing to use RANGE RIGHT if it gets me that. However, I am worried that this may be a bug of some kind and that I should not rely on it (as it may be "fixed" in a later version).

Is this a "feature" that can be relied on?

Scripts:

Range Left
Does Deletes and Inserts

use master
GO
-- Comment this next line out for the first run
DROP database PartitionTest
go
create database PartitionTest
go
use PartitionTest
go
-- Add Filegroups
ALTER DATABASE [PartitionTest] ADD FILEGROUP [DatePartitionTest];
GO
Alter database PartitionTest set recovery simple
go
-- Add Files
ALTER DATABASE [PartitionTest] ADD FILE ( NAME = N'PartitionTest_1', FILENAME = N'D:\PartitionTest_1.ndf') TO FILEGROUP [DatePartitionTest]
GO

-- This is the ONLY part that is different between the two scripts.  LEFT vs RIGHT
CREATE PARTITION FUNCTION [Orders_Id_Function](bigint) AS RANGE LEFT FOR VALUES
(1000,2000,3000)
GO

-- Create partition Scheme
CREATE PARTITION SCHEME [Orders__Scheme] AS PARTITION [Orders_Id_Function] TO
([DatePartitionTest],[DatePartitionTest],[DatePartitionTest],[DatePartitionTest])
-- Create table
CREATE TABLE [dbo].[Orders](
       [OrdDate] [datetime] NOT NULL,
       [ID] [bigint] IDENTITY(1,1) NOT NULL,
       [Addr] varchar(100) NOT NULL)

-- Partition the table
CREATE UNIQUE CLUSTERED INDEX IX_Orders
ON [Orders](OrdDate asc,ID asc)
ON [Orders__Scheme] (ID);
GO
-- Insert rows into  partitions (partition 4 in this case)
Use PartitionTest
set nocount on
go
declare @i int
set @i = 1
declare @date Datetime
while (@i < 1000)
begin
    set @date = dateadd(mi,@i,'2012-11-01T10:17:01.000')
    --insert into testtable values (@date)
    insert into [Orders] values (@date, 'Denzil')
    insert into [Orders] values (dateadd(month,3,@date), 'Denzil')
    insert into [Orders] values (dateadd(month,6,@date), 'Denzil')
    insert into [Orders] values (dateadd(month,9,@date), 'Denzil')
    set @i = @i+1;
END

-- Check the rowcount in each partition
select $PARTITION.[Orders_ID_Function](ID) as PartionNum,COUNT(*) as CountRows from  Orders
Group by $PARTITION.[Orders_ID_Function](ID)

Alter partition scheme [Orders__Scheme] NEXT USED [DatePartitionTest]
GO

-- Set a check point for the log file.
CHECKPOINT
GO

-- Add the new partition
ALTER PARTITION FUNCTION Orders_ID_Function() SPLIT RANGE (4000)

select Operation,count(*) as NumLogRecords from fn_dblog(NULL,NULL)
where AllocUnitName= 'dbo.Orders.IX_Orders'
group by Operation
order by count(*) DESC


SELECT
t.name as TableName,i.name as IndexName,
p.partition_id as partitionID,
p.partition_number,rows, fg.name
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 (t.object_id = p.object_id and i.index_id = p.index_id)
        INNER JOIN sys.destination_data_spaces dds ON (p.partition_number = dds.destination_id)
        INNER JOIN sys.filegroups AS fg ON (dds.data_space_id = fg.data_space_id)
WHERE (t.name = 'Orders') and (i.index_id IN (0,1))

Range Right
Does NOT do Inserts or Deletes

use master
GO
-- Comment this next line out for the first run
DROP database PartitionTest
go
create database PartitionTest
go
use PartitionTest
go
-- Add Filegroups
ALTER DATABASE [PartitionTest] ADD FILEGROUP [DatePartitionTest];
GO
Alter database PartitionTest set recovery simple
go
-- Add Files
ALTER DATABASE [PartitionTest] ADD FILE ( NAME = N'PartitionTest_1', FILENAME = N'D:\PartitionTest_1.ndf') TO FILEGROUP [DatePartitionTest]
GO

-- This is the ONLY part that is different between the two scripts.  LEFT vs RIGHT
CREATE PARTITION FUNCTION [Orders_Id_Function](bigint) AS RANGE RIGHT FOR VALUES
(1000,2000,3000)
GO

-- Create partition Scheme
CREATE PARTITION SCHEME [Orders__Scheme] AS PARTITION [Orders_Id_Function] TO
([DatePartitionTest],[DatePartitionTest],[DatePartitionTest],[DatePartitionTest])
-- Create table
CREATE TABLE [dbo].[Orders](
       [OrdDate] [datetime] NOT NULL,
       [ID] [bigint] IDENTITY(1,1) NOT NULL,
       [Addr] varchar(100) NOT NULL)

-- Partition the table
CREATE UNIQUE CLUSTERED INDEX IX_Orders
ON [Orders](OrdDate asc,ID asc)
ON [Orders__Scheme] (ID);
GO
-- Insert rows into  partitions (partition 4 in this case)
Use PartitionTest
set nocount on
go
declare @i int
set @i = 1
declare @date Datetime
while (@i < 1000)
begin
    set @date = dateadd(mi,@i,'2012-11-01T10:17:01.000')
    --insert into testtable values (@date)
    insert into [Orders] values (@date, 'Denzil')
    insert into [Orders] values (dateadd(month,3,@date), 'Denzil')
    insert into [Orders] values (dateadd(month,6,@date), 'Denzil')
    insert into [Orders] values (dateadd(month,9,@date), 'Denzil')
    set @i = @i+1;
END

-- Check the rowcount in each partition
select $PARTITION.[Orders_ID_Function](ID) as PartionNum,COUNT(*) as CountRows from  Orders
Group by $PARTITION.[Orders_ID_Function](ID)

Alter partition scheme [Orders__Scheme] NEXT USED [DatePartitionTest]
GO

-- Set a check point for the log file.
CHECKPOINT
GO

-- Add the new partition
ALTER PARTITION FUNCTION Orders_ID_Function() SPLIT RANGE (4000)

select Operation,count(*) as NumLogRecords from fn_dblog(NULL,NULL)
where AllocUnitName= 'dbo.Orders.IX_Orders'
group by Operation
order by count(*) DESC


SELECT
t.name as TableName,i.name as IndexName,
p.partition_id as partitionID,
p.partition_number,rows, fg.name
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 (t.object_id = p.object_id and i.index_id = p.index_id)
        INNER JOIN sys.destination_data_spaces dds ON (p.partition_number = dds.destination_id)
        INNER JOIN sys.filegroups AS fg ON (dds.data_space_id = fg.data_space_id)
WHERE (t.name = 'Orders') and (i.index_id IN (0,1))

Best Answer

I thought RANGE LEFT or RANGE RIGHT just controlled if the border value went with the left or right partition. But it clearly does something else too.

Is there more to RANGE LEFT and RANGE RIGHT that I don't understand?

Yes, there is other significance to RANGE LEFT and RIGHT as your scripts demonstrate. The range specification determines if the newly created partition is to the left or right of the existing split partition. The range specification also determines the partition removed by MERGE, which is the partition that includes the existing boundary.

In the first RANGE LEFT script, the existing 4th partition is split into 2 adjacent ones, with the new one on the left. The new partition becomes partition #4 and the existing one is renumbered to #5. Rows from the existing partition (now #5) must be moved to #4 to accommodate the new boundaries, hence the excessive logging. Movement during partition SWITCH (or MERGE) requires about 4x the logging of normal DML operations so it is important to plan as to avoid data movement, especially with large tables that are often used in table partitioning scenarios.

No data movement is needed in the RANGE RIGHT script because the new partition (#5) is created to the right of the existing partition 4 and the existing partition 4 already conforms to those boundaries (data >= 3000 and < 4000).

Personally, I think RANGE RIGHT is more intuitive, especially with incremental partitioning column values. See http://www.dbdelta.com/category/table-partitioning/ for more gotchas.

Related Question