Criar índices com colunas incluídas

  • Artigo

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure

Este artigo descreve como adicionar colunas incluído (ou não chave) para estender a funcionalidade de índices não clusterizados no SQL Server usando o SQL Server Management Studio ou o Transact-SQL. Ao incluir colunas não chave, você pode criar você índices não clusterizados que abrangem mais consultas. Isto porque as colunas não chave têm os seguintes benefícios:

  • Elas podem ser tipos de dados não permitidos como colunas de chave de índice.
  • Elas não são consideradas pelo Mecanismo de Banco de Dados ao calcular o número de colunas de chave de índice ou o tamanho da chave de índice.

Um índice com colunas não chave pode melhorar o desempenho de consulta significativamente quando todas as colunas na consulta são incluídas no índice como colunas de chave ou não chave. Ganhos de desempenho são obtidos porque o otimizador de consulta pode localizar todos os valores da coluna dentro do índice; os dados da tabela ou do índice clusterizado não são acessados, resultando em menos operações de E/S em disco.

Observação

Quando um índice contém todas as colunas referenciadas por uma consulta, ele costuma ser referenciado como se abrangendo a consulta.

Recomendações de design

  • Redesenhe índices não clusterizados que tenham um tamanho grande de chave de índice, de tal forma que apenas as colunas usadas para buscas e pesquisas sejam colunas de chave. Transforme todas as outras colunas que abrangem a consulta em colunas não chave. Dessa forma, você terá todas as colunas necessárias para cobrir a consulta, mas a chave do índice propriamente dita é pequena e eficiente.

  • Inclua colunas não chave em um índice não clusterizado para evitar exceder as limitações do tamanho atual do índice de um máximo de 32 colunas de chave e um tamanho máximo de chave de índice de 1.700 bytes (16 colunas de chave e 900 bytes antes do SQL Server 2016 (13.x)). O Mecanismo de Banco de Dados não considera as colunas que não são chaves ao calcular o número de colunas de chave de índice ou o tamanho da chave de índice.

  • A ordem das colunas não chave na definição do índice não afeta o desempenho das consultas que usam o índice.

  • Evite índices não clusterizados muito largos em que as colunas incluídas não representam um subconjunto suficientemente estreito das colunas da tabela subjacente. Para adicionar índices largos, verifique se o custo da atualização de um índice extra grande desloca o custo de leitura diretamente da tabela.

Limitações e restrições

  • As colunas não chave só podem ser definidas em índices não clusterizados.

  • Todos os tipos de dados, exceto text, ntexte image , podem ser usados como colunas não chave.

  • As colunas computadas que são determinísticas e precisas ou imprecisas podem ser colunas não chave. Para obter mais informações, consulte Índices de colunas computadas.

  • As colunas computadas derivadas dos tipos de dados image, ntexte text podem ser colunas não chave, desde que o tipo de dados da coluna computada seja permitido como uma coluna de índice não chave.

  • As colunas que não são de chave não podem ser removidas de uma tabela, a menos que o índice dessa tabela seja removido primeiro.

  • As colunas não de chave não podem ser alteradas, exceto para fazer o seguinte:

    • Alterar a nulidade da coluna da coluna NOT NULL até NULL.

    • Aumente o tamanho das colunas varchar, nvarcharou varbinary .

Segurança

Permissões

Requer a permissão ALTER na tabela ou exibição. O usuário deve ser membro da função de servidor fixa sysadmin ou das funções de banco de dados fixas db_ddladmin e db_owner .

Usando o SQL Server Management Studio para criar um índice com colunas não chave

  1. No Pesquisador de Objetos, selecione o sinal de adição para expandir o banco de dados que contém a tabela na qual você deseja criar um índice com colunas não chave.

  2. Selecione o sinal de adição para expandir a pasta Tabelas.

  3. Selecione o sinal de adição para expandir a tabela na qual você deseja criar um índice com colunas não chave.

  4. Clique com o botão direito do mouse na pasta Índices, aponte para Novo Índice e selecione Índice Não Clusterizado....

  5. Na caixa de diálogo Novo Índice , na página Geral , insira o nome do novo índice na caixa Nome do índice .

  6. Em Colunas de chave de índice, selecione Adicionar....

  7. Na caixa de diálogo Selecionar colunas de table_name, marque as caixas de seleção das colunas da tabela a serem adicionadas ao índice.

  8. Selecione OK.

  9. Na guia Colunas incluídas, selecione Adicionar....

  10. Na caixa de diálogo Selecionar Colunas detable_name , marque as caixas de seleção das colunas da tabela ou a serem adicionadas ao índice como colunas não chave.

  11. Selecione OK.

  12. Na caixa de diálogo Novo Índice, selecione OK.

Usando o Transact-SQL para criar um índice com colunas não chave

  1. No Pesquisador de Objetos, conecte-se a uma instância do Mecanismo de Banco de Dados.

  2. Na barra Padrão, selecione Nova Consulta.

  3. Copie e cole o exemplo a seguir na janela de consulta e selecione Executar.

    USE AdventureWorks2022;
GO
-- Creates a nonclustered index on the Person.Address table with four included (nonkey) columns.
-- index key column is PostalCode and the nonkey columns are
-- AddressLine1, AddressLine2, City, and StateProvinceID.
CREATE NONCLUSTERED INDEX IX_Address_PostalCode
ON Person.Address (PostalCode)
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);
GO

Conteúdo relacionado