• Artigo

Diretrizes para desabilitar índices

A desabilitação de um índice impede que o usuário acesse o índice, e que os índices clusterizados acessem os dados da tabela subjacente. O Mecanismo de banco de dados do SQL Server pode desabilitar um índice automaticamente durante uma atualização do SQL Server ou você pode desabilitar um índice manualmente. Para obter mais informações, consulte Desabilitando índices.

Qualquer tipo de índice pode ser desabilitado. Quando um índice é desabilitado, são aplicadas as seguintes regras:

  • Se o índice for exclusivo, a restrição PRIMARY KEY ou UNIQUE e todas as restrições FOREIGN KEY que referenciam as colunas indexadas de outras tabelas serão desabilitadas. O usuário que desabilita o índice deve ter permissões ALTER nessas tabelas ou ocorrerá falha na instrução ALTER INDEX DISABLE. Se for um índice clusterizado, todas as restrições FOREIGN KEY de entrada e saída na tabela subjacente serão desabilitadas.

    Os nomes das restrições são listados em uma mensagem de aviso quando o índice é desabilitado. Depois de recompilar o índice, as restrições devem ser habilitadas manualmente usando a instrução ALTER TABLE CHECK CONSTRAINT.

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

  • O otimizador de consulta não considera o índice 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 de um índice desabilitado existente, porque a definição do índice ainda existe nos metadados.

  • Um índice desabilitado pode ser cancelado.

Desabilitando índices não clusterizados

A desabilitação de um índice não clusterizado exclui fisicamente os dados do índice. Porém, a definição do índice continua nos metadados. As seguintes diretrizes adicionais são aplicadas para desabilitar índices não clusterizados:

  • As estatísticas no índice permanecem no local e são atualizadas automaticamente, conforme necessário.

  • 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. Para obter mais informações, consulte Diretrizes para habilitar índices e restrições.

Desabilitando índices clusterizados

As seguintes diretrizes adicionais são aplicadas para desabilitar índices clusterizados:

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

    • Ocorrerá falha nessas operações: instruções SELECT, UPDATE, DELETE, INSERT, CREATE INDEX, CREATE STATISTICS, UPDATE STATISTICS (no índice) e ALTER TABLE que modificam colunas de tabela ou restrições.

    • Essas operações terão êxito: CREATE VIEW, DROP VIEW, CREATE TRIGGER, DROP TRIGGER, DROP INDEX, ALTER TABLE ENABLE/DISABLE TRIGGER, TRUNCATE TABLE e DROP TABLE.

    • Os índices não clusterizados não podem ser criados enquanto o índice clusterizado estiver desabilitado.

  • Os índices não clusterizados e os índices XML existentes associados à tabela são automaticamente desabilitados e não podem ser acessados.

  • Todos os índices clusterizados e não clusterizados em exibições que referenciam a tabela são desabilitados. Esses índices devem ser recompilados da mesma maneira que aqueles da tabela referenciada.

Desabilitando restrições

Essas diretrizes adicionais são aplicadas para desabilitar restrições PRIMARY KEY, FOREIGN KEY e UNIQUE:

  • restrições PRIMARY KEY e UNIQUE são desabilitadas ao se desabilitar o índice associado usando a instrução ALTER INDEX DISABLE.

  • Quando uma restrição PRIMARY KEY é desabilitada, todas as restrições FOREIGN KEY associadas também são desabilitadas. Isso é o equivalente a configurar a opção NOCHECK CONSTRAINT na restrição.

  • Você deve ter permissões ALTER ou CONTROL nas tabelas referenciadas.

  • Se uma ação CASCADE UPDATE ou DELETE for declarada em uma referência de chave estrangeira e aquela referência for desabilitada, haverá falha em qualquer atualização ou instruções de exclusão que faria com que a restrição propagasse a modificação à tabela de referência.

  • Os valores duplicados podem ser acidentalmente adicionados a uma tabela enquanto o índice PRIMARY KEY ou UNIQUE estiver desabilitado ou, em uma atualização SQL Server, pela alteração que fez com que o índice fosse desabilitado. Você deve corrigir as linhas duplicadas manualmente antes de o índice poder ser habilitado com êxito. As seguintes resoluções são possíveis:

    • Remover ou alterar manualmente os valores duplicados.

    • Se o índice UNIQUE não tiver sido criado como resultado da criação de uma restrição UNIQUE, use CREATE INDEX WITH DROP_EXISTING para recriar o índice sem especificar UNIQUE.

    • Se o índice tiver sido criado como um subproduto de uma restrição PRIMARY KEY ou UNIQUE, você deve cancelar a restrição. Em seguida, o índice é cancelado. Para uma restrição PRIMARY KEY, qualquer restrição FOREIGN KEY também deve ser cancelada.

  • As restrições FOREIGN KEY e CHECK que são desabilitadas são marcadas com is_not_trusted. Elas são visíveis nas exibições do catálogo sys.check_constraints e sys.foreign_keys. Isso significa que a restrição já não está sendo verificada pelo sistema para todas as linhas da tabela. Mesmo quando você habilita novamente a restrição, o sistema não verifica novamente as linhas existentes na tabela, a menos que você especifique a opção WITH CHECK do ALTER TABLE. A especificação de WITH CHECK marca a restrição como confiável novamente.

    O exemplo a seguir desabilita uma restrição que limita os salários aceitos nos dados. NOCHECK CONSTRAINT é usada com ALTER TABLE para desabilitar a restrição e permitir uma inserção que normalmente violaria a restrição. O WITH CHECK CHECK CONSTRAINT habilita novamente a restrição, e também valida os dados existentes na restrição que foi habilitada novamente.

    CREATE TABLE cnst_example 
(id INT NOT NULL,
    name VARCHAR(10) NOT NULL,
    salary MONEY NOT NULL
    CONSTRAINT salary_cap CHECK (salary < 100000);
)

-- Disable the constraint.
ALTER TABLE cnst_example NOCHECK CONSTRAINT salary_cap;

-- Reenable the constraint.
ALTER TABLE cnst_example WITH CHECK CHECK CONSTRAINT salary_cap;

Desabilitando índices em exibições

A desabilitação de um índice clusterizado em uma exibição exclui fisicamente os dados do índice. As seguintes diretrizes adicionais se aplicam à desabilitação de índices em exibições:

  • A desabilitação de um índice clusterizado em uma exibição não evita modificações na tabela subjacente.

  • A desabilitação de um índice clusterizado em uma exibição também desabilita qualquer índice não clusterizado naquela exibição.

  • As linhas de dados do índice dos índices clusterizados e não clusterizados são excluídas. Porém, as definições de exibição e de índice permanecem nos metadados e podem ser recriadas por meio da recompilação do índice ou de índices.

  • 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.

  • A recompilação do índice clusterizado em uma exibição não habilita automaticamente os índices não clusterizados na exibição.

  • Os índices não clusterizados devem ser habilitados manualmente, recompilando-os depois da recompilação do índice clusterizado.

Executando operações online de índice em índices desabilitados

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 Executando operações de índice online.

Estatísticas sobre índices desabilitados

Quando o índice é desabilitado, são aplicadas as seguintes restrições às estatísticas do índice:

  • 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.

  • O sp_autostats falha quando a tabela especificada tem um índice clusterizado desabilitado.

  • O sp_updatestats não atualiza estatísticas em índices clusterizados desabilitados.

  • O sp_createstats cria estatísticas em colunas que podem estar conduzindo as colunas de um índice desabilitado. Quando indexonly é especificado, as estatísticas não são criadas em uma coluna em um índice desabilitado, a menos que aquela coluna também seja usada em outro índice habilitado.

Comandos DBCC

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

Exibindo o status de um índice desabilitado

Quando um índice ou restrição PRIMARY KEY ou UNIQUE está desabilitado, uma mensagem de aviso que lista todos os índices afetados e restrições FOREIGN KEY ou CHECK é exibida. Você também pode exibir o status desabilitado de um índice na exibição do catálogo sys.indexes ou usando a função INDEXPROPERTY. É possível exibir o status desabilitado das restrições FOREIGN KEY e CHECK nas exibições do catálogo sys.foreign_keys e sys.check_constraints, respectivamente. Para obter mais informações, consulte Exibindo informações de índice.

Exemplos

O exemplo a seguir desabilita um índice não clusterizado na tabela Employee.

USE AdventureWorks2008R2;
GO
ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode 
ON HumanResources.Employee DISABLE;