Desabilitar índices e restrições

Este tópico descreve como desabilitar um índice ou restrições no SQL Server 2012 usando o SQL Server Management Studio ou o Transact-SQL. A desabilitação de um índice impede que o usuário o acesse, e que índices clusterizados acessem os dados da tabela subjacente. A definição do índice permanece nos metadados e as estatísticas do índice são mantidas em índices não clusterizados. A desabilitação de um índice não clusterizado ou clusterizado em uma exibição exclui fisicamente os dados do índice. A desabilitação de um índice clusterizado em uma tabela impede o acesso aos dados; os dados ainda permanecem na tabela, mas ficam indisponíveis para operações DML (linguagem de manipulação de dados) até que o índice seja descartado ou recriado.

Neste tópico

• Antes de começar:

  Limitações e restrições

  Segurança

• Para desabilitar um índice usando:

  SQL Server Management Studio

  Transact-SQL

Antes de começar

Limitações e restrições

• O índice não é mantido enquanto estiver desabilitado.

• O otimizador de consulta não considera o índice desabilitado ao criar planos de execução de consulta. As consultas que referenciam o índice desabilitado com uma dica de tabela também falham.

• Você não pode criar um índice que usa o mesmo nome que um índice desabilitado existente.

• Um índice desabilitado pode ser cancelado.

• Ao desabilitar um índice exclusivo, a restrição PRIMARY KEY ou UNIQUE e todas as restrições FOREIGN KEY que referenciam as colunas indexadas de outras tabelas também são desabilitadas. Ao desabilitar um índice clusterizado, todas as restrições FOREIGN KEY de entrada e saída na tabela subjacente também são desabilitadas. Os nomes das restrições são listados em uma mensagem de aviso quando o índice é desabilitado. Depois de recompilar o índice, todas as restrições devem ser habilitadas manualmente usando a instrução ALTER TABLE CHECK CONSTRAINT.

• Os índices não clusterizados são desabilitados automaticamente quando o índice clusterizado associado é desabilitado. Eles não podem ser habilitados até o índice clusterizado na tabela ou exibição ser habilitado ou o índice clusterizado na tabela for cancelado. Os índices não clusterizados devem ser explicitamente habilitados, a menos que o índice clusterizado tenha sido habilitado usando a instrução ALTER INDEX ALL REBUILD.

• A instrução ALTER INDEX ALL REBUILD recompila e habilita todos os índices desabilitados na tabela, com exceção dos índices desabilitados nas exibições. Os índices em exibições devem ser habilitados em uma instrução ALTER INDEX ALL REBUILD separada.

• Desabilitar um índice clusterizado em uma tabela também desabilita todos os índices clusterizados e não clusterizados em exibições que referenciam essa tabela. Esses índices devem ser recompilados da mesma maneira que aqueles da tabela referenciada.

• As linhas de dados do índice clusterizado desabilitado não podem ser acessadas, exceto para cancelar ou recompilar o índice clusterizado.

• Você pode recompilar um índice não clusterizado desabilitado online quando a tabela não tiver um índice clusterizado desabilitado. Porém, sempre precisará recompilar um índice clusterizado desabilitado offline se você usar a instrução ALTER INDEX REBUILD ou CREATE INDEX WITH DROP_EXISTING. Para obter mais informações sobre operações de índice online, consulte Executar operações de índice online.

• A instrução CREATE STATISTICS não pode ser executada com êxito em uma tabela que tem um índice clusterizado desabilitado.

• A opção de banco de dados AUTO_CREATE_STATISTICS cria novas estatísticas em uma coluna quando o índice é desabilitado e existem as seguintes condições:

  • AUTO_CREATE_STATISTICS é definido como ON

  • Não há nenhuma estatística existente para a coluna.

  • As estatísticas são exigidas durante a otimização da consulta.

• Se um índice clusterizado for desabilitado, DBCC CHECKDB não poderá retornar informações sobre a tabela subjacente. Em vez disso, a instrução reportará que o índice clusterizado está desabilitado. DBCC INDEXDEFRAG não pode ser usado para desfragmentar um índice desabilitado; a instrução falhará com uma mensagem de erro. Você pode usar DBCC DBREINDEX para recompilar um índice desabilitado.

• Criar um novo índice clusterizado habilita índices não clusterizados previamente desabilitados. Para obter mais informações, consulte Habilitar índices e restrições.

Segurança

Permissões

Para executar ALTER INDEX, no mínimo, a permissão ALTER na tabela ou exibição é necessária.

[Início]

Usando o SQL Server Management Studio

Para desabilitar um índice

1. No Pesquisador de Objetos, clique no sinal de adição ao lado do banco de dados que contém a tabela na qual você deseja desabilitar um índice.

2. Clique no sinal de adição para expandir a pasta Tabelas.

3. Clique no sinal de adição ao lado da tabela na qual você deseja desabilitar um índice.

4. Clique no sinal de adição para expandir a pasta Índices.

5. Clique com o botão direito do mouse no índice a ser desabilitado e selecione Desabilitar.

6. Na caixa de diálogo Desabilitar Índices, verifique se o índice correto está na grade Índices a serem desabilitados e clique em OK.

Para desabilitar todos os índices de uma tabela

1. No Pesquisador de Objetos, clique no sinal de adição para expandir o banco de dados que contém a tabela na qual você deseja desabilitar os índices.

2. Clique no sinal de adição para expandir a pasta Tabelas.

3. Clique no sinal de adição para expandir a tabela na qual você deseja desabilitar os índices.

4. Clique com o botão direito do mouse na pasta Índices e selecione Desabilitar Todos

5. Na caixa de diálogo Desabilitar Índices, verifique se os índices corretos estão na grade Índices a serem desabilitados e clique em OK. Para remover um índice da grade Índices a serem desabilitados, selecione o índice e pressione a tecla Delete.

As informações a seguir estão disponíveis na caixa de diálogo Desabilitar Índices:

• Nome do Índice
  Exibe o nome do índice. Durante a execução, esta coluna exibe também um ícone que representa o status.

• Nome da tabela
  Exibe o nome da tabela ou exibição na qual o índice foi criado.

• Tipo de Índice
  Exibe o tipo de índice: Clusterizado, Não clusterizado, Espacialou XML.

• Status
  Exibe o status atual da operação de desabilitação. Os possíveis valores após a execução são:

  • Em branco

    Antes de execução o Status fica em branco.

  • Em andamento

    A desabilitação dos índices foi iniciada mas não está concluída.

  • Êxito

    A operação de desabilitação foi concluída com êxito.

  • Erro

    Foi encontrado um erro durante a operação de desabilitação do índice e a operação e a operação não foi concluída com êxito.

  • Parado

    A desabilitação do índice não foi concluída com êxito porque o usuário interrompeu a operação.

• Mensagem
  Fornece o texto de mensagens de erro durante a operação de desabilitação. Durante a execução, os erros aparecem como hiperlinks. O texto dos hiperlinks descreve o corpo do erro. A coluna Mensagem raramente é grande o suficiente para acomodar o texto de mensagem completo. Há dois modos para obter o texto completo:

  • Mova o ponteiro de mouse sobre a célula de mensagem para exibir uma dica de ferramenta com o texto do erro.

  • Clique no hiperlink para exibir uma caixa de diálogo que exibe o erro completo.

[Início]

Usando Transact-SQL

Para desabilitar um índice

1. No Pesquisador de Objetos, conecte-se a uma instância do Mecanismo de Banco de Dados.

2. Na barra Padrão, clique em Nova Consulta.

3. Copie e cole o exemplo a seguir na janela de consulta e clique em Executar.

   USE AdventureWorks2012;
   GO
   -- disables the IX_Employee_OrganizationLevel_OrganizationNode index
   -- on the HumanResources.Employee table
   ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee
   DISABLE;
   

Para desabilitar todos os índices de uma tabela

1. No Pesquisador de Objetos, conecte-se a uma instância do Mecanismo de Banco de Dados.

2. Na barra Padrão, clique em Nova Consulta.

3. Copie e cole o exemplo a seguir na janela de consulta e clique em Executar.

   USE AdventureWorks2012;
   GO
   -- Disables all indexes on the HumanResources.Employee table.
   ALTER INDEX ALL ON HumanResources.Employee
   DISABLE;
   

Para obter mais informações, consulte ALTER INDEX (Transact-SQL).

[Início]