Artigo
04/01/2012

Criando índices (Mecanismo de Banco de Dados)

Este tópico descreve as principais tarefas de criação de índice e fornece as diretrizes de implementação e desempenho a serem consideradas antes da criação de um índice.

Tarefas de criação de índices

As seguintes tarefas compõem a estratégia recomendada para a criação de índices:

Crie o índice.

A criação do índice é uma tarefa fundamental. A criação de índices inclui a determinação das colunas a serem usadas, a seleção do tipo de índice (por exemplo, clusterizado ou não clusterizado), a seleção de opções de índice apropriadas e a determinação do posicionamento do grupo de arquivos ou do esquema de partição. Para obter mais informações, consulte Criando índices.
Determine o melhor método de criação. Os índices são criados das seguintes maneiras:
- Definindo uma restrição PRIMARY KEY ou UNIQUE em uma coluna usando CREATE TABLE ou ALTER TABLE
  
  O Mecanismo de banco de dados do SQL Server cria automaticamente um índice exclusivo para forçar os requisitos de exclusividade de uma restrição PRIMARY KEY ou UNIQUE. Por padrão, um índice clusterizado exclusivo é criado para forçar uma restrição PRIMARY KEY, exceto quando já existir um índice clusterizado na tabela, ou quando você especificar um índice não clusterizado exclusivo. Por padrão, um índice não clusterizado exclusivo é criado para forçar uma restrição UNIQUE, a menos que um índice clusterizado exclusivo seja especificado explicitamente e não exista um índice clusterizado na tabela.
  
  Opções de índice e locais de índice, grupo de arquivos ou esquema de partição também podem ser especificados.
  
  Um índice criado como parte de uma restrição PRIMARY KEY ou UNIQUE recebe o mesmo nome da restrição. Para obter mais informações, consulte Restrições PRIMARY KEY e Restrições UNIQUE.
- Criando um índice independente de restrição utilizando a instrução CREATE INDEX ou a caixa de diálogo Novo Índice no Pesquisador de Objetos do SQL Server Management Studio.
  
  Você precisará especificar o nome do índice, da tabela e das colunas aos quais o índice será aplicado. Opções de índice e locais de índice, grupo de arquivos ou esquema de partição também podem ser especificados. Por padrão, um índice não clusterizado e não exclusivo será criado se as opções clusterizadas ou exclusivas não forem especificadas. Para criar um índice filtrado, use a cláusula WHERE opcional. Para obter mais informações, consulte Diretrizes de criação de índice filtrado.
Crie o índice.

O fato de o índice ser criado em uma tabela vazia ou em uma que contenha dados é um fator importante a ser considerado. A criação de um índice em uma tabela vazia não gera implicações de desempenho no momento da criação, entretanto, o desempenho será afetado quando forem adicionados dados à tabela.

A criação de índices em tabelas grandes deve ser cuidadosamente planejada, de modo que o desempenho não seja prejudicado. O modo preferido para criar índices em tabelas grandes é iniciar com o índice clusterizado e, depois, criar índices não clusterizados. Considere a definição da opção ONLINE como ON ao criar índices em tabelas existentes. Quando a opção ON estiver definida, os bloqueios de tabela de longo prazo não serão mantidos, permitindo que as consultas ou as atualizações da tabela subjacente prossigam. Para obter mais informações, consulte Executando operações de índice online.

Considerações sobre implementação

A tabela a seguir lista os valores máximos que se aplicam a índices clusterizados, não clusterizados, espaciais, filtrados e XML. Salvo indicação em contrário, as limitações se aplicam a todos os tipos de índices.

Limites máximos de índice	Valor	Informações adicionais
Índices clusterizados por tabela	1
Índices não clusterizados por tabela	999	Inclui índices não clusterizados criados por restrições PRIMARY KEY ou UNIQUE e índices filtrados, mas não índices XML.
Índices XML por tabela	249	Inclui índices XML primários e secundários em colunas de tipo de dados xml. Índices em colunas de tipo de dados XML
Índices espaciais por tabela	249	Trabalhando com índices espaciais (Mecanismo de Banco de Dados)
Número de colunas de chave por índice	16*	O índice clusterizado será limitado a 15 colunas se a tabela também contiver um índice XML primário ou índices espaciais. Tamanho máximo de chaves de índice.
Tamanho de registro de chave de índice	900 bytes*	Não se aplica a índices XML ou espaciais. Para que uma tabela ofereça suporte a índices espaciais, o tamanho máximo de registro de chave do índice deve ser de 895 bytes. Tamanho máximo de chaves de índice.

*Você pode evitar limitações de tamanho de registro e coluna de chave de índices não clusterizados incluindo colunas não chave no índice. Para obter mais informações, consulte Índice com colunas incluídas.

Tipos de dados

Em geral, qualquer coluna em uma tabela ou exibição pode ser indexada. A tabela a seguir lista os tipos de dados que têm participação de índice restrita.

Tipo de dados	Participação de índice	Informações adicionais
Tipo de dados CLR definido pelo usuário	Poderá ser indexado se o tipo oferecer suporte a ordenação binária.	Trabalhando com tipos de dados CLR definidos pelo usuário
Os tipos de dados LOB (Objeto Grande) são: image, ntext, text, varchar(max), nvarchar(max), varbinary(max) e xml.	Não podem ser uma coluna de chave de índice. No entanto, uma coluna XML pode ser uma coluna de chave em um índice XML primário ou secundário de uma tabela. Podem participar como colunas não chave (incluídas) em um índice não clusterizado, com exceção de image, ntext e text. Podem participar se integrarem uma expressão de coluna computada.	Índice com colunas incluídas Índices em colunas de tipo de dados XML
Colunas computadas	Podem ser indexadas. Incluem colunas computadas definidas como invocações de método de uma coluna de tipo de dados CLR definido pelo usuário, desde que os métodos sejam marcados como determinísticos. As colunas computadas derivadas de tipos de dados LOB podem ser indexadas como coluna de chave ou não chave, desde que o tipo de dados da coluna computada seja permitido como coluna de chave de índice ou coluna de não chave.	Criando índices em colunas computadas
Colunas Varchar extraídas da linha	A chave de índice de um índice clusterizado não pode conter colunas varchar que tenham dados existentes na unidade de alocação ROW_OVERFLOW_DATA. Se um índice clusterizado for criado em uma coluna varchar e os dados existentes estiverem na unidade de alocação IN_ROW_DATA, ocorrerá falha nas ações subsequentes de inserção e atualização na coluna que extraem os dados da linha.	Organização de tabela e índice Dados de estouro de linha excedendo 8 KB
geometry	Pode ser indexado com vários índices espaciais.	Tipos de dados espaciais

Considerações adicionais

Veja algumas considerações adicionais para a criação de índices:

Você pode criar um índice, se tiver as permissões CONTROL ou ALTER na tabela.
Quando criado, o índice é habilitado automaticamente e disponibilizado para uso. Você pode remover o acesso a um índice desabilitando-o. Para obter mais informações, consulte Desabilitando índices.

Requisitos de espaço em disco

O total de espaço em disco exigido para armazenar o índice depende dos seguintes fatores:

Tamanho de cada linha de dados na tabela e número de linhas por página. Isso determina o número de páginas de dados que precisam ser lidas no disco para criar o índice.
Colunas do índice e tipos de dados usados. Isso determina o número de páginas de índice que precisa ser gravado no disco. Para obter mais informações, consulte Estimando o tamanho de um índice clusterizado e Estimando o tamanho de um índice não clusterizado.
Espaço em disco temporário requerido durante o processo de criação de índice. Para obter mais informações, consulte Determinando requisitos de espaço em disco de índice.

Considerações sobre desempenho

O tempo despendido na criação física de um índice é altamente dependente do subsistema do disco. Os fatores importantes a considerar são os seguintes:

Modelo de recuperação do banco de dados. O modelo de recuperação bulk-logged fornece maior desempenho e consumo reduzido de espaço de log durante a operação de criação de índice, do que a recuperação completa. Porém, a recuperação bulk-logged reduz a flexibilidade da recuperação pontual. Para obter mais informações, consulte Escolhendo um modelo de recuperação para operações de índice.
O RAID nível RAID (redundant array of independent disks) usado para armazenar o banco de dados e os arquivos de log de transações. Em geral, os níveis RAID que usam a divisão de dados terão largura da banda de E/S superior.
Número de discos na matriz de discos, caso RAID tenha sido usado. Mais unidades na matriz aumentam as taxas de transferência de dados de forma proporcional.
Local onde são armazenadas as execuções de classificação intermediárias dos dados. O uso da opção SORT_IN_TEMPDB pode reduzir o tempo necessário à criação de um índice quando tempdb estiver em um conjunto de discos que não seja o banco de dados de usuário. Para obter mais informações, consulte tempdb e criação de índice.
Criando o índice offline ou online.

Quando um índice é criado offline (padrão), são mantidos bloqueios exclusivos na tabela subjacente até que a transação que cria o índice seja concluída. A tabela é inacessível a usuários enquanto o índice está sendo criado.

Com exceção de índices XML e espaciais, você pode especificar que o índice seja criado online. Quando a opção online está definida como ON, os bloqueios de tabela de longo prazo não são mantidos, o que permite que as consultas ou as atualizações da tabela subjacente prossigam durante a criação do índice. Embora as operações de índice online sejam recomendadas, você deve avaliar o ambiente e os requisitos específicos. Talvez seja melhor executar operações de índice offline. Com isso, os usuários teriam acesso restrito aos dados durante a operação, mas a operação terminaria mais rapidamente e usaria menos recursos. Para obter mais informações, consulte Executando operações de índice online.