Recommandations pour la désactivation des index

Désactivation d'index non-cluster

La désactivation d'un index non-cluster supprime physiquement les données de l'index. Toutefois, la définition de l'index demeure dans les métadonnées. Les directives supplémentaires suivantes s'appliquent à la désactivation d'index non-cluster :

• Les statistiques sur l'index demeurent en place et sont, le cas échéant, automatiquement mises à jour.

• Les index non-cluster sont automatiquement désactivés lorsque l'index cluster associé est désactivé. Ils demeurent désactivés tant que l'index cluster de la table ou de la vue n'est pas activé ou que l'index cluster de la table n'est pas supprimé. Les index non-cluster doivent être explicitement activés, sauf si l'index cluster a été activé à l'aide de l'instruction ALTER INDEX ALL REBUILD. Pour plus d'informations, consultez Indications sur l'activation des index et des contraintes.

Désactivation d'index cluster

Les directives supplémentaires suivantes s'appliquent à la désactivation d'index cluster :

• Les lignes de données de l'index cluster désactivé ne sont accessibles que pour supprimer ou reconstruire cet index. Par conséquent :

  • Les opérations suivantes échouent : instructions SELECT, UPDATE, DELETE, INSERT, CREATE INDEX, CREATE STATISTICS, UPDATE STATISTICS (sur l'index) et ALTER TABLE qui modifient les contraintes ou les colonnes de table.

  • Les opérations suivantes réussissent : CREATE VIEW, DROP VIEW, CREATE TRIGGER, DROP TRIGGER, DROP INDEX, ALTER TABLE ENABLE/DISABLE TRIGGER, TRUNCATE TABLE et DROP TABLE.

  • Les index non-cluster ne peuvent pas être créés alors que l'index cluster est désactivé.

• Les index non-cluster et les index XML existants associés à la table sont automatiquement désactivés et sont inaccessibles.

• La totalité des index cluster et non-cluster appartenant à des vues qui référencent la table sont désactivés. Ces index doivent être recréés comme ceux appartenant à la table référencée.

Désactivation des contraintes

Ces recommandations supplémentaires s'appliquent à la désactivation des contraintes PRIMARY KEY, FOREIGN KEY et UNIQUE :

• Pour désactiver les contraintes PRIMARY KEY et UNIQUE, vous devez désactiver l'index associé à l'aide de l'instruction ALTER INDEX DISABLE.

• La désactivation d'une contrainte PRIMARY KEY entraîne celle de toutes les contraintes FOREIGN KEY associées. Cette opération est similaire à l'activation de l'option NOCHECK CONSTRAINT sur la contrainte.

• Vous devez disposer des autorisations ALTER ou CONTROL sur les tables référencées.

• Si une action CASCADE UPDATE ou DELETE est déclarée sur une référence de clé étrangère et que cette référence est désactivée, toute mise à jour ou instruction de suppression susceptible d'amener la contrainte à propager la modification à la table de références échoue.

• Des valeurs en double peuvent être ajoutées par inadvertance à une table alors que l'index PRIMARY KEY ou UNIQUE est désactivé ou, dans une mise à niveau SQL Server, suite à la modification qui a désactivé l'index. Vous devez corriger manuellement les lignes en double pour que l'index puisse être correctement activé. Les solutions suivantes sont possibles :

  • Supprimez ou modifiez manuellement les valeurs en double.

  • Si l'index UNIQUE n'a pas été généré suite à la création d'une contrainte UNIQUE, utilisez l'instruction CREATE INDEX WITH DROP_EXISTING pour recréer l'index sans spécifier UNIQUE.

  • Si la création de l'index est un effet secondaire d'une contrainte PRIMARY KEY ou UNIQUE, vous devez supprimer celle-ci. L'index est alors supprimé. Dans le cas d'une contrainte PRIMARY KEY, toutes les contraintes FOREIGN KEY doivent également être supprimées.

• Les contraintes FOREIGN KEY et CHECK qui sont désactivées sont marquées is_not_trusted. Vous pouvez les afficher dans les affichages catalogue sys.check_constraints et sys.foreign_keys. Cela signifie que la contrainte n'est plus vérifiée par le système pour toutes les lignes de la table. Même si vous réactivez la contrainte, elle ne vérifie pas de nouveau les lignes existantes dans la table sauf si vous spécifiez l'option WITH CHECK de l'instruction ALTER TABLE. La spécification de l'option WITH CHECK marque de nouveau la contrainte comme étant approuvée.

  L'exemple suivant désactive la contrainte définissant les salaires pouvant être inclus dans les données. L'option NOCHECK CONSTRAINT est utilisée avec ALTER TABLE pour désactiver la contrainte et permettre une insertion qui devrait généralement entraîner une violation de la contrainte. L'option WITH CHECK CHECK CONSTRAINT réactive la contrainte et, en outre, valide les données existantes par rapport à la contrainte réactivée.

  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;
  

Désactivation d'index de vues

La désactivation d'un index cluster sur une vue supprime physiquement les données de l'index. Les recommandations supplémentaires suivantes s'appliquent à la désactivation d'index sur des vues :

• La désactivation d'un index cluster sur une vue n'empêche pas de modifier la table sous-jacente.

• La désactivation d'un index cluster sur une vue entraîne celle de tous les index non-cluster sur cette vue.

• Les lignes de données des index cluster et non-cluster sont supprimées. Toutefois, les définitions de vue et d'index demeurent dans les métadonnées et vous pouvez les recréer en reconstruisant les index.

• L'instruction ALTER INDEX ALL REBUILD recrée et active tous les index désactivés sur la table, sauf les index désactivés sur des vues. Les index sur des vues doivent être activés dans une instruction ALTER INDEX ALL REBUILD distincte.

• La reconstruction de l'index cluster sur une vue n'active pas automatiquement les index non-cluster sur cette vue.

• Vous devez activer manuellement les index non-cluster en les reconstruisant après avoir reconstruit l'index cluster.

Réalisation d'opérations en ligne sur les index désactivés

Vous pouvez reconstruire un index non-cluster désactivé en ligne lorsque la table ne possède pas d'index cluster désactivé. Toutefois, vous devez toujours reconstruire un index cluster désactivé hors ligne si vous utilisez l'instruction ALTER INDEX REBUILD ou CREATE INDEX WITH DROP_EXISTING. Pour plus d'informations sur les opérations d'index en ligne, consultez Exécution d'opérations en ligne sur les index.

Statistiques sur les index désactivés

Les restrictions suivantes s'appliquent aux statistiques d'un index désactivé :

• L'instruction CREATE STATISTICS ne peut pas être correctement exécutée sur une table qui possède un index cluster désactivé.

• L'option de base de données AUTO_CREATE_STATISTICS crée de nouvelles statistiques sur une colonne lorsque l'index est désactivé et que les conditions suivantes sont réunies :

  • AUTO_CREATE_STATISTICS a pour valeur ON.

  • Il n'existe pas de statistiques sur la colonne.

  • Des statistiques sont requises pendant l'optimisation de la requête.

• sp_autostats échoue lorsque la table spécifiée possède un index cluster désactivé.

• sp_updatestats ne met pas à jour les statistiques des index cluster désactivés.

• sp_createstats crée des statistiques sur des colonnes qui peuvent être des colonnes de début d'un index désactivé. Lorsque indexonly est spécifié, les statistiques ne sont pas créées sur une colonne dans un index désactivé sauf si cette colonne est également utilisée dans un autre index activé.

Commandes DBCC

Si un index cluster est désactivé, DBCC CHECKDB ne peut pas retourner des informations sur la table sous-jacente. Par contre, l'instruction signale que l'index cluster est désactivé. L'instruction DBCC INDEXDEFRAG ne peut pas être utilisée pour défragmenter un index désactivé. Elle échoue et affiche un message d'erreur. Vous pouvez utiliser l'instruction DBCC DBREINDEX pour recréer un index désactivé.

Affichage de l'état d'un index désactivé

Lorsqu'un index ou une contrainte PRIMARY KEY ou UNIQUE est désactivé, un message d'avertissement répertoriant l'ensemble des index et des contraintes FOREIGN KEY ou CHECK affectés apparaît. En outre, vous pouvez afficher l'état désactivé d'un index dans l'affichage catalogue sys.indexes ou à l'aide de la fonction INDEXPROPERTY. Vous pouvez afficher l'état désactivé des contraintes FOREIGN KEY et CHECK dans les affichages catalogue sys.foreign_keys et sys.check_constraints. Pour plus d'informations, consultez Affichage des informations sur les index.

Exemple

L'exemple ci-dessous désactive un index non-cluster sur la table Employee.

USE AdventureWorks;
GO
ALTER INDEX IX_Employee_ManagerID 
ON HumanResources.Employee DISABLE;

Voir aussi

Référence

Concepts