Partilhar via

  • 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 cluster. 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 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 AdventureWorks2008R2 :

     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 AdventureWorks2008R2;
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 AdventureWorks2008R2;
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 AdventureWorks2008R2;
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.