Why Does Partition Range Right Skip Deletes/Inserts?

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 AS RANGE LEFT FOR VALUES
(1000,2000,3000

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 https://www.dbdelta.com/table-partitioning-best-practices/ for more gotchas.

StackExchange Database Administrators Q#103273, answer score: 9