Desabilitar índices e restrições

  • Artigo

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure

Este artigo descreve como desabilitar um índice ou restrições no SQL Server 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. Desabilitar um índice clusterizado em uma exibição ou um índice não clusterizado exclui fisicamente os dados do índice.

Desabilitar um índice clusterizado em uma tabela evita que o usuário acesse os 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.

Limitaçõ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 falha com uma mensagem de erro. Você pode usar DBCC DBREINDEX para recriar um índice desabilitado.

Criar um novo índice clusterizado habilita índices não clusterizados previamente desabilitados. Para obter mais informações, consulte Enable Indexes and Constraints.

Se a tabela for um heap, todos os índices não clusterizados são recriados.

Permissões

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

Usar o SQL Server Management Studio

Desabilitar um índice

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

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

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

  4. Selecione o 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.

    Observação

    Se a tabela estiver aberta no modo Design, o controle Desabilitar não estará disponível. Para prosseguir, feche o designer de tabela e comece de novo.

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

Desabilitar todos os índices de uma tabela

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

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

  3. Selecione o 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 selecione 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 da execução, o Status está 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.

  • Message

    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.
    • Selecione o hiperlink para exibir uma caixa de diálogo que exibe o erro completo.

Usar o Transact-SQL

Os exemplos de código do Transact-SQL deste artigo usa o banco de dados de exemplo AdventureWorks2022 ou AdventureWorksDW2022, que pode ser baixado da home page Microsoft SQL Server Samples and Community Projects.

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, selecione Nova Consulta.

  3. Copie e cole o exemplo a seguir na janela de consulta e selecione Executar. Este exemplo desabilita o índice IX_Employee_OrganizationLevel_OrganizationNode na tabela HumanResources.Employee.

    USE AdventureWorks2022;
GO

ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode
    ON HumanResources.Employee
DISABLE;

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, selecione Nova Consulta.

  3. Copie e cole o exemplo a seguir na janela de consulta e selecione Executar. Este exemplo desabilita todos os índices na tabela HumanResources.Employee.

    USE AdventureWorks2022;
GO

ALTER INDEX ALL ON HumanResources.Employee
DISABLE;

Conteúdo relacionado