Desativar índices e restrições

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada SQL do Azurebanco de dados SQL no Microsoft Fabric

Este artigo descreve como desabilitar um índice ou restrições no SQL Server usando o SQL Server Management Studio ou o Transact-SQL. A desativação de um índice impede o acesso do usuário ao índice e aos índices agrupados aos 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 agrupados. A desativação de um índice clusterizado em um modo de exibição ou de um índice não clusterizado exclui fisicamente os dados do índice.

A desativação de um índice clusterizado em uma tabela impede o acesso aos dados. Os dados ainda permanecem na tabela, mas não estão disponíveis para operações de linguagem de manipulação de dados (DML) até que o índice seja descartado ou reconstruído.

Limitações

O índice não é mantido enquanto está desativado.

O otimizador de consulta não considera o índice desabilitado ao criar planos de execução de consulta. Além disso, as consultas que fazem referência ao índice desativado com uma dica de tabela falham.

Não é possível criar um índice que use o mesmo nome de um índice desabilitado existente.

Um índice desativado pode ser removido.

Quando você desabilita um índice exclusivo, a restrição de PRIMARY KEY ou UNIQUE e todas as restrições FOREIGN KEY que fazem referência às colunas indexadas de outras tabelas também são desabilitadas. Quando você desabilita um índice clusterizado, todas as restrições de entrada e saída de FOREIGN KEY na tabela subjacente também são desabilitadas. Os nomes de restrição são listados em uma mensagem de aviso quando o índice é desativado. Depois de reconstruir 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 automaticamente desativados quando o índice clusterizado associado é desativado. Eles não podem ser habilitados até que o índice clusterizado na tabela ou exibição esteja habilitado ou o índice clusterizado na tabela seja descartado. 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 recria e habilita todos os índices desabilitados na tabela, exceto os índices desabilitados em modos de exibição. Os índices em visualizações devem ser ativados numa instrução ALTER INDEX ALL REBUILD separada.

A desativação de um índice clusterizado em uma tabela também desabilita todos os índices clusterizados e não clusterizados em modos de exibição que fazem referência a essa tabela. Esses índices devem ser reconstruídos da mesma forma que os índices na tabela referenciada.

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

Você pode reconstruir um índice não clusterizado desabilitado online quando a tabela não tiver um índice clusterizado desabilitado. No entanto, deve-se sempre reconstruir offline um índice clusterizado desativado ao 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 tenha um índice clusterizado desabilitado.

A opção de banco de dados AUTO_CREATE_STATISTICS cria novas estatísticas em uma coluna quando o índice está desativado e as seguintes condições são atendidas:

• AUTO_CREATE_STATISTICS está definido para ON.
• Não existem estatísticas para a coluna.
• As estatísticas são necessárias durante a otimização da consulta.

Se um índice clusterizado estiver desabilitado, DBCC CHECKDB não poderá retornar informações sobre a tabela subjacente; em vez disso, a instrução informa que o índice clusterizado está desabilitado. DBCC INDEXDEFRAG não pode ser usado para desfragmentar um índice desativado; A instrução falha com uma mensagem de erro. Você pode usar DBCC DBREINDEX para reconstruir um índice desativado.

A criação de um novo índice clusterizado permite índices não clusterizados anteriormente desativados. Para obter mais informações, consulte Habilitar índices e restrições.

Se a tabela for um heap, todos os índices não clusterizados serão reconstruídos.

Permissões

Para executar ALTER INDEX, no mínimo, é necessário possuir ALTER permissão na tabela ou vista.

Usar o SQL Server Management Studio

Desativar um índice

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 um índice.

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 um índice.

4. Selecione o sinal de mais para expandir a pasta Índices.

5. Clique com o botão direito do rato no índice que pretende desativar e selecione Desativar.

   Observação

   Se a tabela estiver aberta no modo Design, o controlo Desativar não estará disponível. Para prosseguir, feche o projetista de tabelas e comece novamente.

6. Na caixa de diálogo Desativar Índices, verifique se o índice correto está na grade 'Índices para desativar' e selecione OK.

Desabilitar todos os índices em 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 símbolo de mais para expandir a pasta Tabelas.

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

4. Clique com o botão direito do rato na pasta Índices e selecione Desativar Tudo.

5. Na caixa de diálogo Desativar Índices, verifique se os índices corretos estão na grelha Índices para desativar e selecione OK. Para remover um índice do Índices para desativar grade, selecione o índice e pressione a tecla Delete.

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

• Nome do índice

  Exibe o nome do índice. Durante a execução, esta coluna também exibe 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 do índice: Agrupado, Não agrupado, Espacialou XML.

• Status

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

  • Em branco

    Antes da execução, o Status está em branco.

  • Em curso

    A desativação dos índices foi iniciada, mas não foi concluída.

  • Sucesso

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

  • Erro

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

  • Parou

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

• Mensagem

  Fornece o texto de mensagens de erro durante a operação de desativação. Durante a execução, os erros aparecem como hiperlinks. O texto dos hiperlinks descreve o corpo do erro. A coluna Mensagem raramente é ampla o suficiente para ler o texto completo da mensagem. Há duas maneiras de obter o texto completo:

  • Mova o ponteiro do mouse sobre a célula da mensagem para exibir uma dica de ferramenta com o texto de erro.
  • Selecione o hiperlink para exibir uma caixa de diálogo exibindo o erro completo.

Use Transact-SQL

Os exemplos de código neste artigo usam o banco de dados de exemplo AdventureWorks2022 ou AdventureWorksDW2022, que você pode baixar da home page Microsoft SQL Server Samples and Community Projects.

Desativar 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 desativa o índice de 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 em 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 desativa todos os índices na tabela HumanResources.Employee.

   USE AdventureWorks2022;
   GO
   
   ALTER INDEX ALL ON HumanResources.Employee
   DISABLE;
   

Conteúdo relacionado

• ALTERAR ÍNDICE (Transact-SQL)