SQL Server – Moving Rows Between Partitions by Updating Partition Key

partitioningsql serversql-server-2005sql-server-2008

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?

Best Answer

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.

Related Question