Artigo
04/01/2012

Tamanho máximo de chaves de índice

Quando você cria um índice que contém muitas colunas chave, ou colunas de tamanho-grande, calcule o tamanho da chave de índice para verificar se você não excede o tamanho máximo da chave de índice. O SQL Server retém o limite de 900 bytes para o tamanho máximo total de todas as colunas de chave de índice. Isso exclui colunas não chave incluídas na definição de índices não clusterizados.

Calculando o tamanho de uma chave de índice

Para calcular o tamanho de uma chave de índice, siga estas etapas.

Exiba as propriedades das colunas de tabela nas quais o índice será baseado. Você pode fazer isso usando a exibição de catálogo sys.columns.

Some o comprimento de cada coluna que será definida na chave de índice.

Por exemplo, a instrução a seguir agrega a coluna max_length da exibição de catálogo sys.columns das colunas especificadas na tabela Person.Address.

USE AdventureWorks2008R2;
GO
SELECT SUM(max_length)AS TotalIndexKeySize
FROM sys.columns
WHERE name IN (N'AddressLine1', N'AddressLine2', N'City', N'StateProvinceID', N'PostalCode')
AND object_id = OBJECT_ID(N'Person.Address');

Observação
Se uma coluna de tabela for um tipo de dados Unicode como nchar ou nvarchar, o comprimento de coluna exibido será o comprimento de armazenamento da coluna. Isso é duas vezes o número de caracteres especificado na instrução CREATE TABLE. No exemplo anterior, City está definido como um tipo de dados nvarchar(30); portanto, o comprimento de armazenamento da coluna é 60.

Se o comprimento total for menor do que 900 bytes, as colunas poderão participar como colunas de chave de índice. Se o comprimento total exceder 900 bytes, revise as informações de opções e considerações adicionais a seguir.

A instrução CREATE INDEX usa os seguintes algoritmos para calcular o tamanho da chave de índice:
- Se o tamanho de todas as colunas de chave fixas, mais o tamanho máximo de todas as colunas de chave variáveis especificadas na instrução CREATE INDEX for inferior a 900 bytes, a instrução CREATE INDEX será finalizada sem advertências ou erros.
- Se o tamanho de todas as colunas de chave fixas mais o tamanho máximo de todas as colunas de chave variáveis exceder 900, mas o tamanho de todas colunas de chave fixas mais o tamanho mínimo das colunas de chave variáveis for menor que 900, a instrução CREATE INDEX terá êxito com um aviso de que uma instrução subsequente INSERT ou UPDATE poderá falhar se especificar valores que gerem um valor chave maior que 900 bytes. A instrução CREATE INDEX falha quando linhas de dados existentes na tabela têm valores que geram uma chave maior que 900 bytes. Uma instrução subsequente INSERT ou UPDATE falha ao especificar valores de dados que geram um valor de chave maior que 900 bytes.
- Se o tamanho de todas as colunas de chave fixas mais o tamanho mínimo de todas as colunas de chave variáveis especificadas na instrução CREATE INDEX exceder 900 bytes, a instrução CREATE INDEX falhará.
A tabela a seguir resume os resultados da criação de índices que satisfazem ou excedem o índice máximo das restrições de tamanho chave.

O tamanho mínimo de colunas de comprimento variável + Tamanho de colunas de dados fixas.	O tamanho máximo de colunas de comprimento variável + Tamanho de colunas de dados fixas.	O MÁXIMO da SOMA dos comprimentos de coluna de chave de índice para linhas existentes*	O índice é criado	Tipo de mensagem	Erro em tempo de execução INSERT ou UPDATE causado pelo valor de chave de índice superdimensionado.
> 900 bytes	Não relevante	Não relevante	Nenhum	Erro	Nenhum índice presente para gerar erro.
<= 900 bytes	<= 900 bytes	Não relevante	Sim	Nenhum	Nenhum.
<= 900 bytes	> 900 bytes	<= 900 bytes	Sim	Aviso	Somente se a soma dos comprimentos atuais de todas as colunas de índice for maior que 900 bytes.
<= 900 bytes	> 900 bytes	> 900 bytes	Nenhum	Erro	Nenhum índice presente para gerar erro.

* Nenhuma das linhas da tabela no momento da execução da instrução CREATE INDEX pode ter valores de chave de índice com comprimento total que exceda 900 bytes.

Usando colunas incluídas para evitar limites de tamanho

Você pode incluir colunas não chave em um índice não clusterizado para evitar limitações atuais de tamanho de índice de, no máximo, de 16 colunas chave e tamanho de chave de índice de, no máximo, 900 bytes. O Mecanismo de banco de dados do SQL Server não considera colunas não chave ao calcular o número de colunas de chave de índice ou o tamanho total das colunas de chave de índice. Em um índice não clusterizado com colunas incluídas, o tamanho total das colunas de chave de índice é restrito a 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. As colunas na cláusula INCLUDE podem ser de todos os tipos de dados, exceto text, ntext e image.

Observação
Quando as tabelas são particionadas, se as colunas de chave de particionamento não estiverem presentes em um índice clusterizado não exclusivo, elas são adicionadas ao índice pelo Mecanismo de Banco de Dados. O tamanho combinado das colunas indexadas (sem contar as colunas incluídas), mais as colunas de particionamento adicionadas, não pode exceder 1.800 bytes em um índice clusterizado não exclusivo.

Quando as tabelas são particionadas, se as colunas de chave de particionamento não estiverem presentes em um índice clusterizado não exclusivo, elas são adicionadas ao índice pelo Mecanismo de Banco de Dados. O tamanho combinado das colunas indexadas (sem contar as colunas incluídas), mais as colunas de particionamento adicionadas, não pode exceder 1.800 bytes em um índice clusterizado não exclusivo.