Can I move rows between partitions by updating the partition key?
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.
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.