Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

up vote 11 down vote accepted

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.

share|improve this answer
1  
that's a nicely done piece of answer! –  Marian Aug 9 '11 at 21:20
    
This executes as you describe in SQL Server 2005 as well –  Ben Brocka Oct 26 '11 at 21:31
    
-1 This does not test the scenario. $PARTITION only computes the partition number based on the input; it doesn't actually test where the row physically lives. –  Jon Seigel Oct 3 '13 at 17:04

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.

share|improve this answer
1  
+1 for the first answer to this question that actually tests the scenario. Welcome to DBA.SE! –  Jon Seigel Oct 3 '13 at 17:02
    
-1 Can you please point me to the MSDN documents that support your requirements to 'fully' partition a table? Specifically the necessity for separate file-groups and a clustered index? –  Kenneth Nov 18 '13 at 22:38

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.

share|improve this answer
2  
Searching for $partition over here suggests that the accepted answer is correct. How are you confirming that the record stays in the same partition after it has been updated? –  Nick Chammas Oct 26 '11 at 21:38
    
The first point is true, but the conclusion that the row doesn't move is false -- presumably there's something wrong with the test that was run. –  Jon Seigel Oct 3 '13 at 17:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.