Índice com colunas incluídas

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çã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.

Consulte também

Conceitos