Há uma infinidade de informações disponíveis detalhando por que um IDENTITYcampo deve ser usado como chave primária e índice clusterizado de uma tabela para a maioria das situações; ainda estou tendo dificuldade em decidir se minha situação particular é uma exceção.
Eu tenho uma tabela com cerca de 250 colunas - não quero iniciar um debate de normalização - e cerca de 100 milhões de linhas. A tabela é compactada por página. Em dias de pico, cerca de 1 milhão de linhas são inseridas na tabela sequencialmente com 0 contenção de outras conexões. A combinação de um char(2) NOT NULLcampo e um int NOT NULLcampo é garantidamente única e nunca muda. Existem apenas cerca de 10 valores exclusivos para o char(2)campo.
Meu argumento para uma IDENTITYcoluna sendo usada como o índice clusterizado é baseado apenas no desempenho. Como as inserções ocorrerão sequencialmente, não devo me preocupar com a contenção de travas; assim as inserções devem ser mais rápidas, pois não haverá necessidade de pesquisar o índice. Por outro lado, uma chave composta de 6 bytes é bastante pequena; e vou acabar tornando-a a chave primária de qualquer maneira (com o char(2)campo primeiro). Além disso, haverá algumas outras tabelas que são carregadas em massa com base nas novas linhas nesta tabela (provavelmente por meio de um índice filtrado). Se eu usar uma IDENTITYcoluna, provavelmente usarei essa coluna como o índice clusterizado e a chave primária dessas outras tabelas (sem a IDENTITYpropriedade) e não trarei a chave composta "natural".
Fazer um índice não clusterizado na chave composta anula o aumento de velocidade esperado de usar uma IDENTITYcoluna como o índice clusterizado, pois esse índice terá que ser pesquisado quando as inserções forem concluídas?
Editar
Com base nos comentários, alterei o título e estou fazendo uma pergunta corrigida cuja resposta pode ser usada para responder à pergunta original acima. Como a manutenção de um índice clusterizado exclusivo se compara à manutenção de um índice não clusterizado exclusivo em relação às inserções? Um índice clusterizado não sequencial sofre de forma semelhante a um índice não clusterizado não sequencial?
Embaraçosamente, eu nunca soube de índices columnstore. Esta tabela é usada principalmente para análise; assim, usei um índice columnstore clusterizado com uma chave primária definida nos campos
char(2)eintconforme a sugestão de David Browne - Microsoft nos comentários.Como a pergunta era sobre manutenção de índice clusterizado em comparação com manutenção de índice não clusterizado, "responderei" a essa parte da melhor maneira possível. Pelo que li, são necessários mais recursos quando um índice clusterizado tem uma divisão de página do que uma divisão de página em um índice não clusterizado menor (com a mesma chave). Isso ocorre porque a página contém muito mais dados no índice clusterizado. Conseqüentemente, se eu não tivesse usado um índice clusterizado columnstore, teria usado (e testado) uma
IDENTITYcoluna para o índice clusterizado. Dito isso, o vídeo sp_BlitzErik fornecido nos comentários é muitoinformativo — e facilmente digerível para plebes como eu — e destaca como há muitas recomendações desatualizadas "por aí" baseadas em implementações mais antigas do SQL Server; assim, as preocupações com a fragmentação externa podem ser exageradas.