Sql-server – move rows between partitions by updating the 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.