Posso mover linhas entre partições atualizando a chave de partição?

Não tenho um servidor 2005 para testar. 2008, no entanto, parece lidar com isso como esperado:

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 ;

Você deve ver um registro em cada partição antes da atualização e ambos os registros na primeira partição depois.

Para testar isso, o experimento realmente precisa particionar a tabela. Consulte http://www.kodyaz.com/articles/how-to-partition-table-non-partitioned-table-sql-server-2008.aspx

Consultar a função de particionamento apenas informa o que a função de particionamento diz. Não diz onde os dados são armazenados. Você pode configurar uma função de particionamento e executá-la sem realmente particionar uma tabela, como já foi demonstrado aqui.

Para particionar a tabela, você também precisa criar grupos de arquivos e um esquema de particionamento que use a função de particionamento para atribuir resultados de função a grupos de arquivos. Então você tem que colocar uma chave em cluster na tabela que usa esse esquema de particionamento.

Configure o particionamento

Não sou especialista em SQL de linha de comando. Usei a interface do SSMS para configurar os grupos de arquivos pfg1 (com um arquivo pf1) e pfg2 (com um arquivo pf2). Em seguida, declarei a função e o esquema de particionamento:

CREATE PARTITION FUNCTION IDRange1 (int)
AS RANGE LEFT FOR VALUES (10) ;
GO

CREATE PARTITION SCHEME ps_IDRange1
AS PARTITION IDRange1
TO (pfg1, pfg2)
GO

Crie a tabela e o índice clusterizado

CREATE TABLE [IDRanges](
    [ID] [int] NOT NULL
)
GO

CREATE CLUSTERED INDEX PK_IDRanges
ON dbo.IDRanges(id) ON ps_IDRange1 (ID)
GO

Depois de fazer isso, ao consultar sys.partitions (eu tenho 2005), você verá que a tabela agora tem duas partições em vez de apenas uma para a tabela. Isso indica que implementamos totalmente o particionamento para esta tabela.

select * from sys.partitions where object_id = object_id('IDRanges')

partition_id object_id index_id partition_number hobt_id linhas
-------------------- ----------- ----------- -------- -------- -------------------- --------------------
72057597780295680 770674389 1 1 72057597780295680 0
72057597780361216 770674389 1 2 72057597780361216 0

Agora que temos duas partições (com uma contagem de linhas para cada), podemos realizar um experimento.

Insira as linhas

INSERT INTO IDRanges ([ID]) VALUES (17)
INSERT INTO IDRanges ([ID]) VALUES (7)

Verifique o sys.partitions para ver o que aconteceu.

select * from sys.partitions where object_id = object_id('IDRanges')

partition_id object_id index_id partition_number hobt_id linhas
-------------------- ----------- ----------- -------- -------- -------------------- --------------------
72057597780295680 770674389 1 1 72057597780295680 1
72057597780361216 770674389 1 2 72057597780361216 1

Sim. Uma linha em cada partição.

Mova uma linha.

UPDATE IDRanges
SET [ID] = 8 WHERE [ID] = 17

Verifique as partições

select * from sys.partitions where object_id = object_id('IDRanges')

partition_id object_id index_id partition_number hobt_id linhas
-------------------- ----------- ----------- -------- -------- -------------------- --------------------
72057597780295680 770674389 1 1 72057597780295680 2
72057597780361216 770674389 1 2 72057597780361216 0

A primeira partição agora tem duas linhas em vez de 1 e a segunda partição tem zero linhas em vez de duas.

Acho que isso confirma que a linha foi movida automaticamente como resultado da modificação da chave agrupada em uma tabela particionada.

Post antigo mas muito útil!!

Eu provei que ele se move no SQL Server 2017 truncando a partição 2 após a atualização e o registro ainda está lá na partição 1

TRUNCATE TABLE IDRanges WITH (PARTITIONS(2))

select * from sys.partitions where object_id = object_id('IDRanges')

partition_id object_id index_id partition_number hobt_id     rows
------------ --------- -------- ---------------- ----------- ----
7.20576E+16  34099162  1        1                7.20576E+16 2   
7.20576E+16  34099162  1        2                7.20576E+16 0   

Não acho que essa resposta esteja correta. Quando você usa o valor

 $PARTITION.IDRange1([ID]) AS Partition

você está simplesmente recalculando qual deveria ser a partição, não onde o registro está atualmente.

Você deveria usar:

select * from sys.partitions where object_id = object_id('IDRanges')

Nos meus testes no sql 2005 o valor muda mas o registro fica na mesma partição. Isso provavelmente irá mexer com as estatísticas e com o otimizador, pois ele será executado em um modo multiencadeado, esperando que uma partição esteja em um intervalo específico. Também estará completamente errado quando tentar usar a eliminação de partição para consultar apenas a partição relevante. Acho que você precisa excluir e reinserir cada registro para movê-los.