Dada alguma tabela com uma chave primária, por exemplo:
CREATE TABLE Customers (
CustomerID int NOT NULL PRIMARY KEY,
FirstName nvarchar(50),
LastName nvarchar(50),
Address nvarchar(200),
Email nvarchar(260)
--...
)
temos uma chave primária única em CustomerID.
Tradicionalmente, posso precisar de alguns índices de cobertura adicionais; por exemplo, para encontrar rapidamente um usuário usando CustomerIDou Email:
CREATE INDEX IX_Customers_CustomerIDEmail ON Customers
(
CustomerID,
Email
)
E esses são os tipos de índices que criei por décadas.
Não é necessário ser único, mas na verdade é
O próprio índice existe para evitar uma varredura de tabela; é um índice de cobertura para auxiliar no desempenho (o índice não existe como uma restrição para impor exclusividade).
Hoje lembrei-me de uma pequena informação - o SQL Server pode usar o fato de que:
- uma coluna tem uma restrição de chave estrangeira
- uma coluna tem um índice único
- uma restrição é confiável
para ajudá-lo a otimizar sua execução de consulta. Na verdade, do Guia de design do índice do SQL Server :
Se os dados forem exclusivos e você desejar a exclusividade aplicada, a criação de um índice exclusivo em vez de um índice não exclusivo na mesma combinação de colunas fornece informações adicionais para o otimizador de consulta que pode produzir planos de execução mais eficientes . A criação de um índice exclusivo (de preferência criando uma restrição UNIQUE) é recomendada nesse caso.
Dado que meu índice de várias colunas contém a chave primária, esse índice composto será de fato exclusivo. Não é uma restrição que eu particularmente precise que o SQL Server aplique durante cada inserção ou atualização; mas o fato é que esse índice não agrupado é exclusivo.
Existe alguma vantagem em marcar esse índice único de fato como realmente único?
CREATE UNIQUE INDEX IX_Customers_CustomerIDEmail ON Clientes ( Identificação do Cliente, E-mail )
Parece-me que o SQL Server pode ser inteligente o suficiente para perceber que meu índice já é exclusivo pelo fato de conter a chave primária.
- Mas talvez ele não saiba disso, e há uma vantagem para o otimizador se eu declarar o índice como único de qualquer maneira.
- Exceto, talvez, que agora possa levar a lentidão durante inserções e atualizações, onde deve executar verificações de exclusividade - onde antes nunca precisava antes.
- A menos que saiba que o índice já é exclusivo , porque contém a chave primária.
Não consigo encontrar nenhuma orientação da Microsoft sobre o que fazer quando um índice composto contém a chave primária.
Os benefícios dos índices exclusivos incluem o seguinte:
- A integridade dos dados das colunas definidas é garantida.
- Informações adicionais úteis para o otimizador de consulta são fornecidas.
Devo marcar um índice composto como único se ele já contiver a chave primária? Ou o SQL Server pode descobrir isso sozinho?
Provavelmente não. O otimizador geralmente pode usar informações sobre a exclusividade da coluna de chave contida de qualquer maneira, portanto, não há nenhuma vantagem real.
Há também uma consequência importante de marcar um índice exclusivo em planos de atualização que modificam as chaves desse índice a serem consideradas:
Configurar
Plano de atualização por índice (índice não exclusivo)
Plano de execução:
O otimizador geralmente toma uma decisão baseada em custo entre atualizar índices não clusterizados por linha (um plano 'estreito') ou por índice (um plano 'amplo'). A estratégia padrão (exceto para tabelas OLTP na memória) é um plano amplo.
Planos estreitos (onde os índices não clusterizados são mantidos ao mesmo tempo que o heap/índice clusterizado) são uma otimização de desempenho para pequenas atualizações. Essa otimização não é implementada para todos os casos - usar certos recursos (como exibições indexadas) significa que o(s) índice(s) associado(s) será(ão) mantido(s) em um plano amplo.
Mais informações: Otimizando consultas T-SQL que alteram dados
Nesse caso, usei o sinalizador de rastreamento não documentado 8790 para forçar um amplo plano de atualização: O plano, portanto, mostra os índices clusterizados e não clusterizados sendo mantidos separadamente.
O Split transforma cada atualização em um par delete & insert separado; o filtro filtra todas as linhas que não resultariam em uma alteração no índice.
Mais informações: ( Atualizações sem atualização ) pela equipe de QO do SQL Server.
Plano de atualização por índice (índice exclusivo)
Plano de execução:
Observe os operadores adicionais Sort e Collapse quando o índice é marcado como exclusivo.
Esse padrão Split-Sort-Collapse é necessário ao atualizar as chaves de um índice exclusivo, para evitar violações intermediárias de chaves exclusivas.
Mais informações: Manutenção de índices exclusivos por Craig Freedman
O Sort em particular pode ser um problema. Além de ser um custo extra desnecessário, ele pode vazar para o disco se as estimativas forem imprecisas.
Sobre chaves não clusterizadas
Outro fator a ser considerado é que as estruturas de índice não clusterizadas são sempre exclusivas, em todos os níveis do índice, mesmo que
UNIQUEnão sejam especificadas. A(s) chave(s) de clustering - e possivelmente um unificador se o índice clusterizado não estiver marcado como exclusivo - são adicionados a um índice não clusterizado não exclusivo em todos os níveis.Como consequência, a seguinte definição do índice:
...na verdade contém as chaves (Email, CustomerID) em todos os níveis. É, portanto, 'buscável' em ambas as colunas:
Mais informações: Mais sobre chaves de índice não clusterizadas por Kalen Delaney
O SQL já sabe que é exclusivo (se incluir o PK, não pode ser mais exclusivo), independentemente de você dizer isso explicitamente.
A grande diferença entre um índice não exclusivo e um índice exclusivo é que os índices não exclusivos exigem a chave de índice clusterizado (com valor unificador se o CIX não for declarado como exclusivo) nos níveis mais altos do índice, não apenas na folha nível.
No seu caso, você já tem o CIX na chave, o que significa que já estará em todos os níveis do índice.
Mas você pode criar uma tabela que tenha um PK (único) e um CIX separados (não importa). Em seguida, crie um índice não exclusivo que inclua o PK em sua chave. Coloque algumas linhas na tabela, incluindo alguns valores varchar facilmente localizáveis para sua coluna CIX. Coloque linhas suficientes para causar vários níveis de seus índices. Em seguida, você pode usar DBCC IND para localizar as páginas em seu NCIX e DBCC PAGE para abrir algumas para examinar os dados, para ver se os valores da chave CIX estão nos níveis mais altos.