A ordem da coluna da chave primária agrupada composta em uma tabela particionada?

Qual das seguintes chaves primárias (a ordem das colunas é diferente) é preferida?

Como todas as decisões de indexação, muito depende de como a tabela será consultada.

Todos os índices particionados (para SQL Server 2008 e posterior) têm o ID da partição (não o valor da chave de particionamento ) como uma coluna de chave inicial oculta em cada índice particionado, portanto, as definições concorrentes efetivas são:

PartitionID, K1, K2
contra
PartitionID, K2, K1

Isso afeta a utilidade de cada índice para diferentes tipos de consultas, como seria de esperar. A principal consideração extra é que as buscas de desigualdade na primeira chave real (K1 ou K2) ainda são suportadas, independentemente de qualquer busca de desigualdade e/ou operações de eliminação de partição na coluna PartitionID .

Por exemplo, a especificação de índice (K1, K2) pode buscar um intervalo de partições e um intervalo de valores K1 simultaneamente:

SELECT T1.* 
FROM dbo.T1 AS T1
WHERE 1 = 1
AND T1.K1 >= CONVERT(date, '20080711', 112)
AND T1.K1 <= CONVERT(date, '20100711', 112);

A mesma consulta em uma tabela com (K2, K1) como a chave de índice clusterizado poderia procurar encontrar o intervalo de partições, mas teria que varrer completamente cada partição qualificada para localizar as linhas que correspondem exatamente aos predicados K1. Para ser claro, o teste de valores K1 seria aplicado como um predicado residual, não como uma operação de busca.

Isso será exibido como uma varredura de índice clusterizado no plano de execução, com uma busca de eliminação de partição e um predicado residual nos valores K1:

Um ponto sutil ao usar o tipo de dados de data como a chave de particionamento é que você deve ter cuidado ao usar tipos de data explícitos em suas consultas se espera que a eliminação da partição ocorra de maneira confiável. O uso de outros tipos, como datetime , é feito facilmente (por acidente), mas geralmente impede a eliminação, onde seria logicamente esperado.

Por exemplo, esta consulta tocará todas as partições:

DECLARE @dt datetime = '20080711';

SELECT * 
FROM dbo.T1 AS T1
WHERE T1.K1 = @dt;

Considerando que esta consulta afetará apenas uma única partição:

DECLARE @dt date = '20080711';

SELECT * 
FROM dbo.T1 AS T1
WHERE T1.K1 = @dt;

Ambas as consultas parecem superficialmente idênticas no plano de execução gráfico (uma busca de índice agrupado). Você precisa verificar as propriedades do operador em detalhes para verificar se a eliminação de partição estática ou dinâmica está sendo aplicada.

Para o exemplo de consulta de junção fornecida na pergunta: Ambas as estratégias de indexação incluem a coluna K2, mas nenhuma delas geralmente pode fornecer linhas na ordem K2 sem uma classificação. Como resultado, qualquer índice é igualmente bom para um hash ou junção de loops aninhados, mas nenhum deles pode fornecer a ordem de entrada necessária para uma junção de mesclagem em K2.

Isso pode parecer contra-intuitivo para o índice (K2, K1), mas lembre-se da chave PartitionID inicial . Cada partição tem linhas na ordem (K2, K1). A menos que exatamente uma partição seja especificada na consulta, uma classificação será necessária para retornar as linhas na ordem K2. O índice (K1, K2) só pode retornar linhas na ordem K2 para uma única partição e um único valor fornecido de K1.

A chave primária em cluster proposta (K1, K2) tem a vantagem potencial de minimizar as divisões de página da tabela base se os dados anexados forem realmente classificados pela chave de clustering durante a operação de inserção. Para o índice (K1, K2), isso significaria linhas classificadas por (PartitionID, K1, K2). Para (K2, K1), seria (PartitionID, K2, K1).

Leitura relacionada: Melhorias no processamento de consultas em tabelas e índices particionados

Como você está fazendo sua chave primária em cluster, se você olhar neste artigo da Technet sobre o alinhamento de índices com partições , ele menciona na seção Índices clusterizados que, se você não incluir a coluna de particionamento no índice clusterizado, isso será feito para você. Ambas as opções incluem a coluna de particionamento, portanto, isso não é um problema, mas provavelmente deve ser lembrado.

Tudo isso dito, nada do que li diz que faz alguma diferença, desde que o índice clusterizado contenha a coluna de particionamento (tornando-o alinhado). Pessoalmente, eu provavelmente colocaria a coluna particionada primeiro. Na minha opinião, isso parece permitir que o SQL decida em qual partição procurar antes de examinar qualquer outra coluna no índice.

Com base nas informações fornecidas, não há necessidade real de ter PK e CIX separados neste caso. Isso obviamente pressupõe que a combinação de K1, K2 é única.

Você não precisa incluir sua chave de partição em seu índice clusterizado se a própria chave primária não for particionada.

Você pode criar uma coluna de identidade para servir como a chave primária e o valor agrupado, mas particionar a tabela por outro valor.

Essa seria minha preferência porque resultaria em índices menores porque a chave do cluster é menor.

CREATE TABLE [T] (TId int identity, K1 date, K2 int, K3 varchar(10), ...) 
ON PartitionScheme(K1)

ALTER TABLE T ADD PRIMARY KEY CLUSTERED (TId) ON [primary]

se você tiver um campo de identidade, PK deve estar em 'K1' e 'id_filed'

create table  T (...) 
on partitionscheme(K1)

alter table T add primary key clustred (K1,id_field) on partitionscheme(K1)

se não

alter table T add primary key clustred (K1) on partitionscheme(K1)

, mas o índice clusterizado sempre deve estar no mesmo grupo de arquivos que a tabela.