Quando uma chave primária deve ser declarada sem cluster?

A questão não é 'quando o PK deve ser NC', mas você deve perguntar 'qual é a chave apropriada para o índice clusterizado'?

E a resposta realmente depende de como você consulta os dados . O índice clusterizado tem uma vantagem sobre todos os outros índices: como sempre inclui todas as colunas, está sempre cobrindo. Portanto, as consultas que podem alavancar o índice clusterizado certamente não precisam usar pesquisas para satisfazer algumas das colunas e/ou predicados projetados.

Outra peça do quebra-cabeça é como um índice pode ser usado ? Existem três padrões típicos:

• sondas, quando um único valor de chave é procurado no índice
• varreduras de intervalo, quando um intervalo de valores de chave é recuperado
• ordem por requisitos, quando um índice pode satisfazer uma ordem sem exigir uma classificação stop-and-go

Portanto, se você analisar sua carga esperada (as consultas) e descobrir que um grande número de consultas usaria um índice específico porque usa um determinado padrão de acesso que se beneficia de um índice, faz sentido propor esse índice como o índice clusterizado.

Ainda outro fator é que a chave de índice clusterizado é a chave de pesquisa usada por todos os índices não clusterizados e, portanto, uma chave de índice clusterizada ampla cria um efeito cascata e amplia todos os índices não clusterizados e índices amplos significam mais páginas, mais E/S , mais memória, menos bondade.

Um bom índice clusterizado é estável , não muda durante o tempo de vida da entidade, porque uma alteração nos valores da chave do índice clusterizado significa que a linha deve ser excluída e inserida novamente.

E um bom índice clusterizado cresce para não aleatoriamente (cada valor de chave recém-inserido é maior que o valor anterior) para evitar divisões de página e fragmentação (sem mexer com FILLFACTORs).

Agora que sabemos o que é uma boa chave de índice clusterizado, a chave primária (que é uma propriedade lógica de modelagem de dados) atende aos requisitos? Se sim, então o PK deve ser agrupado. Se não, então o PK não deve ser agrupado.

Para dar um exemplo, considere uma tabela de fatos de vendas. Cada entrada tem um ID que é a chave primária. Mas a grande maioria das consultas pede dados entre uma data e outra data, portanto, a melhor chave de índice clusterizado seria a data de venda , não o ID . Outro exemplo de ter um índice clusterizado diferente da chave primária é uma chave de seletividade muito baixa, como uma 'categoria' ou um 'estado', uma chave com apenas poucos valores distintos. Ter uma chave de índice clusterizado com essa chave de baixa seletividade como a chave mais à esquerda, por exemplo (state, id), geralmente faz sentido devido a varreduras de intervalos que procuram todas as entradas em um 'estado' específico.

Uma última observação sobre a possibilidade de uma chave primária não clusterizada em um heap (ou seja, não há nenhum índice clusterizado). Este pode ser um cenário válido, o motivo típico é quando o desempenho da inserção em massa é crítico, uma vez que os heaps têm um rendimento de inserção em massa significativamente melhor quando comparados aos índices clusterizados.

A razão básica para usar índices clusterizados é declarada na Wikipedia :

O clustering altera o bloco de dados em uma determinada ordem distinta para corresponder ao índice, resultando no armazenamento dos dados de linha em ordem. Portanto, apenas um índice clusterizado pode ser criado em uma determinada tabela de banco de dados. Índices agrupados podem aumentar muito a velocidade geral de recuperação, mas geralmente apenas quando os dados são acessados ​​sequencialmente na mesma ordem ou na ordem inversa do índice agrupado ou quando um intervalo de itens é selecionado.

Digamos que eu tenha uma tabela de Pessoas e essas pessoas tenham uma coluna País e uma Chave Primária exclusiva. É uma tabela demográfica, então essas são as únicas coisas que me interessam; qual país e quantas pessoas únicas estão ligadas a esse país.

Assim, é provável que eu SELECT WHERE ou ORDER BY na coluna Country; um índice clusterizado na Chave Primária não me faz bem, não estou acessando esses dados por PK, estou acessando por essa outra coluna. Como só posso ter um índice clusterizado em uma tabela, declarar meu PK como Clusterizado me impediria de usar um Índice Clusterizado no País.

Além disso, aqui está um bom artigo sobre Índices clusterizados vs não clusterizados, verifica-se que os índices clusterizados causaram problemas de desempenho de inserção no SQL Server 6.5 (o que, pelo menos, esperamos que não seja relevante para a maioria de nós aqui).

Se você colocar um índice clusterizado em uma coluna IDENTITY, todas as suas inserções acontecerão na última página da tabela - e essa página será bloqueada pela duração de cada IDENTITY. Não é grande coisa... a menos que você tenha 5.000 pessoas que querem a última página. Então você tem muita disputa por essa página

Observe que este não é o caso em versões posteriores.

Se sua chave primária for do UNIQUEIDENTIFIERtipo , certifique-se de especificar que é NONCLUSTERED. Se você fizer isso em cluster, cada inserção terá que fazer um monte de embaralhamento de registros para inserir a nova linha na posição correta. Isso irá reduzir o desempenho.

Um exemplo muito comum:

• Customermesa com CustomerIDcomoCLUSTERED PRIMARY KEY
• Tabela de pedidos com OrderID (PK), CustomerID, OrderDatee algumas outras colunas
• OrderPositionscomOrderPositionID (PK), OrderId, ProductID, Amount, Price ...
• você tem que indexar as tabelas de pedidos

Claro que "depende" é - como quase sempre - a resposta correta, mas a maioria dos aplicativos (não BI-Reports) funcionará com base no cliente (por exemplo, você faz login como cliente 278 no site e clica em "Meus pedidos" ou o atendente lista todos os pedidos do cliente 4569 ou sua rotina de faturas resumirá todos os pedidos do cliente 137).

Nesse caso, não faria muito sentido agrupar a tabela pelo OrderID. Sim, você terá consultas SELECT ... WHERE OrderId = ?para listar os detalhes do pedido, mas isso geralmente seria uma busca de índice curta e barata (3 leituras).

Por outro lado, se você agrupasse sua Ordertabela por CustomerID, ela não teria que fazer várias pesquisas de chave toda vez que você consultasse a tabela por CustomerId = ?.

O CLUSTERED INDEXdeve ser sempre UNIQUE, caso contrário, o SQL Server adicionaria uma coluna INT invisível (= inutilizável) UNIQUIFIERpara garantir a unicidade - e faria muito mais sentido adicionar dados reais (utilizáveis) do que algumas coisas aleatórias (dependendo da ordem de inserção).

Como um cliente fará (espero) mais de um pedido, teríamos que adicionar o OrderIDou (se você costuma classificar por isso) o OrderDate(se for uma data e hora - caso contrário, o cliente estaria limitado a um pedido por dia) para o CLUSTERED INDEXe termina com:

CREATE UNIQUE CLUSTERED INDEX IX_Orders_UQ on Orders (CustomerID, OrderID)

As mesmas regras se aplicam à OrderPositionsmesa. Normalmente, a maioria das consultas listará todas as posições em uma ordem específica, então você deve criar o PK com o OrderPositionIDas NONCLUSTEREDe um UNIQUE CLUSTERED INDEXon OrderId, OrderPositionID.

BTW: é correto que a Customertabela seja agrupada por seu PK (o CustomerID, porque é uma "Tabela de nível superior" e será - em um aplicativo típico - principalmente consultada por seu CustomerID.

Tabelas de pesquisa puras como por exemplo Gendersou InvoiceTypesou PaymentTypesão outro exemplo de tabelas que devem ser agrupadas por seu PK (porque você geralmente as unirá em GenderId, InvoiceTypeIdou PaymentTypeId).

Quando um índice clusterizado é considerado mais benéfico para o sistema geral do que um PK clusterizado usando alguma medida de desempenho. Só pode haver um índice clusterizado em uma tabela.

Exemplos de medidas de desempenho são tempo de consulta única (velocidade), integração do tempo total de consulta em relação à tabela (eficiência) e ter que adicionar muitas colunas de inclusão a um índice não clusterizado muito grande para obter desempenho semelhante ao clusterizado (tamanho ).

Isso pode acontecer quando os dados geralmente são recuperados usando um índice que não é exclusivo, contém nulos (não permitido em um PK) ou o PK foi adicionado por um motivo secundário (como replicação ou identificação de registro de trilha de auditoria).