Can I move rows between partitions by updating the partition key?

Question

I would think that this would be a fairly simply question, but I've actually had a difficult time finding an answer for this.

The question: Can you move rows of data within a partitioned table from one partition to another by simply updating the partition column so that it crosses the partition boundary?

For example, if I have a table that has a partition key:

CREATE TABLE SampleTable
(
    SampleID INT PRIMARY KEY,
    SampleResults VARCHAR(100) NOT NULL,
)

With the partition function that maps to the primary key:

CREATE PARTITION FUNCTION MyPartitionFunc (INT) AS
RANGE LEFT FOR VALUES (10000, 20000);

Can I move a row from the first partition to the third partition by changing the SampleID from 1 to (say) 500,000?

Note: I'm tagging this as both sql server 2005 and 2008, since they both support partitioning. Do they handle it differently?

Answer 1

I do not have a 2005 server to test with. 2008 however, appears to handle this as expected:

USE [Test]
GO
CREATE TABLE [IDRanges](
    [ID] [int] NOT NULL
)
GO

CREATE PARTITION FUNCTION IDRange1 (int)
AS RANGE LEFT FOR VALUES (10) ;
GO
--Add one record to each partition
INSERT INTO IDRanges ([ID]) VALUES (17)
INSERT INTO IDRanges ([ID]) VALUES (7)
GO
--Verify records in partition
SELECT $PARTITION.IDRange1([ID]) AS Partition, COUNT(*) AS [COUNT] 
FROM IDRanges
GROUP BY $PARTITION.IDRange1([ID]) 
ORDER BY Partition ;
GO
--Move row between partitions
UPDATE IDRanges
SET [ID] = 8 WHERE [ID] = 17
GO
--Verify records in partition
SELECT $PARTITION.IDRange1([ID]) AS Partition, COUNT(*) AS [COUNT] 
FROM IDRanges
GROUP BY $PARTITION.IDRange1([ID]) 
ORDER BY Partition ;

You should see one record in each partition before the update, and both records in the first partition afterwards.

Answer 2

To test this, the experiment actually needs to partition the table. See http://www.kodyaz.com/articles/how-to-partition-table-non-partitioned-table-sql-server-2008.aspx

Querying the partitioning function just tells you what the partitioning function says. It doesn't say where the data is stored. You can set up a partitioning function and run it without actually partitioning a table, as has been demonstrated here already.

In order to partition the table, you also have to create file groups and a partitioning scheme that uses the partitioning function to assign function results to file groups. Then you have to put a clustered key on the table that uses that partitioning scheme.

Set up the partitioning

I'm no expert in command line SQL. I used SSMS interface to set up file groups pfg1 (with a pf1 file) and pfg2 (with a pf2 file). Then I declared the partitioning function and scheme:

CREATE PARTITION FUNCTION IDRange1 (int)
AS RANGE LEFT FOR VALUES (10) ;
GO

CREATE PARTITION SCHEME ps_IDRange1
AS PARTITION IDRange1
TO (pfg1, pfg2)
GO

Create the table and clustered index

CREATE TABLE [IDRanges](
    [ID] [int] NOT NULL
)
GO

CREATE CLUSTERED INDEX PK_IDRanges
ON dbo.IDRanges(id) ON ps_IDRange1 (ID)
GO

After you do this, when you query sys.partitions (I have 2005), you see that the table now has two partitions instead of just one for the table. This indicates that we have fully implemented partitioning for this table.

select * from sys.partitions where object_id = object_id('IDRanges')

partition_id         object_id   index_id    partition_number hobt_id              rows
-------------------- ----------- ----------- ---------------- -------------------- --------------------
72057597780295680    770674389   1           1                72057597780295680    0
72057597780361216    770674389   1           2                72057597780361216    0

Now that we have two partitions (with a row count for each), we can conduct an experiment.

Insert the rows

INSERT INTO IDRanges ([ID]) VALUES (17)
INSERT INTO IDRanges ([ID]) VALUES (7)

Check the sys.partitions to see what happened.

select * from sys.partitions where object_id = object_id('IDRanges')

partition_id         object_id   index_id    partition_number hobt_id              rows
-------------------- ----------- ----------- ---------------- -------------------- --------------------
72057597780295680    770674389   1           1                72057597780295680    1
72057597780361216    770674389   1           2                72057597780361216    1

Yep. One row in each partition.

Move a row.

UPDATE IDRanges
SET [ID] = 8 WHERE [ID] = 17

Check the partitions

select * from sys.partitions where object_id = object_id('IDRanges')

partition_id         object_id   index_id    partition_number hobt_id              rows
-------------------- ----------- ----------- ---------------- -------------------- --------------------
72057597780295680    770674389   1           1                72057597780295680    2
72057597780361216    770674389   1           2                72057597780361216    0

The first partition now has two rows instead of 1, and the second partition has zero rows instead of two.

I think this confirms that the row was automatically moved as a result of modifying the clustered key in a partitioned table.

Answer 3

Old post but very helpful!!

I proved it does move in SQL Server 2017 by truncating partition 2 after the update and the record is still there in partition 1

TRUNCATE TABLE IDRanges WITH (PARTITIONS(2))

select * from sys.partitions where object_id = object_id('IDRanges')

partition_id object_id index_id partition_number hobt_id     rows
------------ --------- -------- ---------------- ----------- ----
7.20576E+16  34099162  1        1                7.20576E+16 2   
7.20576E+16  34099162  1        2                7.20576E+16 0   

Answer 4

I don't think that answer is correct. When you use the value

 $PARTITION.IDRange1([ID]) AS Partition

you are simply recalculating what the partition should be, not where the record currently is.

You should use:

select * from sys.partitions where object_id = object_id('IDRanges')

In my tests on sql 2005 the value changes but the record stays in the same partition. This will probably mess with stats and the optimizer as it will run in a multi threaded mode expecting a partition to be in a specific range. It will also be completely wrong when it tries to use partition elimination to only query the relevant partition. I think you need to delete and re-insert each record to get them to move.