• Artigo

Índice com colunas incluídas

Você pode estender a funcionalidade de índices não-clusterizados acrescentando colunas de não-chave ao nível folha do índice não-clusterizado. 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.

  • Eles não são considerados 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 incluídas pode melhorar o desempenho de consulta significativamente quando todas as colunas na consulta forem incluídas no índice como colunas chave ou não-chave. Os ganhos de desempenho são alcançados pois o otimizador de consulta pode localizar todos os valores de coluna dentro do índice, a tabela, ou dados de índice clusterizado não são acessados, resultando em poucas operações de E/S de disco.

ObservaçãoObservação

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

Enquanto as colunas chave são armazenadas em todos os níveis do índice, as colunas não-chave são armazenadas apenas em nível folha. Para obter mais informações sobre os níveis de índice, consulte Organização de tabela e índice.

Usando colunas incluídas para evitar limites de tamanho

Você pode incluir 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 16 colunas chave, e um máximo de tamanho chave de índice de 900 bytes. O Mecanismo de Banco de Dados não considera as colunas não-chave ao calcular o número de colunas chave de índice, ou o tamanho da chave do índice.

Por exemplo, suponha que você quer indexar as colunas seguintes na tabela Document no banco de dados de exemplo do AdventureWorks :

     Title nvarchar(50)

     Revision nchar(5)

     FileName nvarchar(400)

Em razão dos tipos de dados nchar e nvarchar requisitarem 2 bytes para cada caractere, um índice que contenha essas três colunas ultrapassariam a limitação de tamanho de 900 byte por 10 bytes (455 * 2). Ao usar a cláusula INCLUDE da declaração CREATE INDEX , a chave de índice pode ser definida como uma coluna não-chave (Title, Revision) e FileName . Desse modo, o tamanho da chave de índice seria de 110 bytes (55 * 2), e o índice ainda conteria todas as colunas necessárias. A seguinte declaração cria tal índice.

USE AdventureWorks;
GO
CREATE INDEX IX_Document_Title       
ON Production.Document (Title, Revision)       
INCLUDE (FileName);

Índice com diretrizes das colunas incluídas

Quando você projeta índices não-clusterizados com colunas incluídas, considere as seguintes diretrizes:

  • As colunas não-chave estão definidas na cláusula INCLUDE da instrução CREATE INDEX.

  • As colunas não-chave só podem ser definidas em índices não-clusterizados em tabelas, ou em exibições indexadas.

  • São permitidos todos os tipos de dados, exceto text, ntext, e image.

  • As colunas computadas que sejam determinísticas, precisas ou imprecisas, podem ser colunas incluídas. Para obter mais informações, consulte Criando índices em colunas computadas.

  • Assim como com as colunas chave, as colunas computadas derivadas dos tipos de dados image, ntext, e text podem ser colunas não-chave (incluídas), desde que o tipo de dados da coluna computada seja permitido como uma coluna de índice não-chave.

  • Os nomes das colunas não podem ser especificados na lista INCLUDE e na lista de coluna chave.

  • Os nomes das colunas não podem ser repetidos na lista INCLUDE.

Diretrizes do tamanho da coluna

  • Pelo menos uma coluna chave deve ser definida. O número de máximo de colunas não-chave é de 1023 colunas. Esse é o número máximo de colunas de tabela menos 1.

  • As colunas chave de índice, exceto as não-chave, devem seguir as restrições de tamanho de índice de no máximo 16 colunas chave, e um tamanho total de chave de índice de no máximo 900 bytes.

  • O tamanho total de todas as colunas não-chave está limitado somente pelo tamanho especificado das colunas na cláusula INCLUDE; por exemplo, as colunas varchar(max) estão limitadas a 2 GB.

Diretrizes para modificação de coluna

Quando você modifica uma coluna de tabela que estava definida como uma coluna incluída, as restrições seguintes se aplicam:

  • As colunas não-chave não podem ser soltar das tabelas, a menos que o índice seja solto antes.

  • As colunas não-chave não podem ser alteradas, exceto para fazerem o seguinte:

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

    • Aumentar o comprimento das colunas varchar, nvarchar, ou varbinary .

      ObservaçãoObservação

      Estas restrições de modificação de coluna também se aplicam para indexar colunas chave.

Recomendações de design

Redesenhe índices não-clusterizados com um comprimento de chave de índice, de tal forma que apenas as colunas usadas para buscas e pesquisas sejam colunas chave. Faça todas as outras colunas que abrangem a consulta colunas não-chave incluídas. Deste modo, você terá todas as colunas necessárias para abranger a consulta, mas a chave de índice em si é pequena e eficiente.

Por exemplo, suponha que você quer projetar um índice para abranger a consulta seguinte.

USE AdventureWorks;
GO
SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN N'98000' and N'99999';

Para abranger a consulta, cada coluna deve ser definida no índice. Embora você possa definir todas as colunas como colunas chave, o tamanho chave seria de 334 bytes. Em razão da única coluna de fato usada como critério de pesquisa ser a coluna PostalCode, que tem um comprimento de 30 bytes, um melhor design de índice definiria PostalCode como sendo a coluna chave e incluiria todas as outras colunas como colunas não-chave.

A seguinte declaração cria um índice com colunas incluídas para abranger a consulta.

USE AdventureWorks;
GO
CREATE INDEX IX_Address_PostalCode       
ON Person.Address (PostalCode)       
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);

Considerações sobre desempenho

Evitando a adição desnecessária de colunas Adicionar muitas colunas de índice, sejam elas chave ou não, pode gerar as seguintes implicações no desempenho:

  • Poucas filas de índice se ajustarão em uma página. Isto poderia criar aumentos de E/S e eficiência de cache reduzida.

  • Será necessário mais espaço em disco para armazenar o índice. Em particular, acrescentar os tipos de dados varchar(max), nvarchar(max), varbinary(max)ou xml como colunas de índice não-chave pode aumentar significativamente os requisitos de espaço em disco. Isto porque os valores de coluna são copiados no nível folha de índice. Portanto, eles residem no índice e na tabela base.

  • A manutenção do índice pode aumentar o tempo necessário para executar modificações, inserções, atualizações ou exclusões, para a tabela subjacente ou exibição indexada.

Você terá que determinar se os ganhos no desempenho de consulta superam o efeito no desempenho durante a modificação de dados, e em requisitos adicionais de espaço em disco. Para obter mais informações sobre como avaliar o desempenho de uma consulta, veja Ajuste de consulta.