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?