我认为这将是一个相当简单的问题,但实际上我很难找到答案.
问题是：
您是否可以通过简单地更新分区列以使其跨越分区边界,将分区表中的数据行从一个分区移动到另一个分区？
例如,如果我有一个具有分区键的表：
CREATE TABLE SampleTable
(
SampleID INT PRIMARY KEY,SampleResults VARCHAR(100) NOT NULL,)
使用映射到主键的分区函数：
CREATE PARTITION FUNCTION MyPartitionFunc (INT) AS RANGE LEFT FOR VALUES (10000,20000);
我可以通过将SampleID从1更改为(例如)500,000来将行从第一个分区移到第三个分区吗？
解决方法
我没有要测试的2005服务器.然而,2008似乎正如预期的那样处理：
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 ;
您应该在更新之前在每个分区中看到一条记录,然后在第一个分区中看到两条记录.
