Désactiver les index et les contraintes

  • Article

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Cette rubrique explique comment désactiver un index ou des contraintes dans SQL Server à l'aide de SQL Server Management Studio ou de Transact-SQL. La désactivation d'un index empêche l'accès des utilisateurs à celui-ci et, s'il s'agit d'un index cluster, aux données de la table sous-jacente. La définition de l'index reste présente dans les métadonnées et les statistiques sont conservées sur les index non cluster. Désactiver un index en cluster sur une vue ou un index non cluster supprime physiquement les données de l’index.

Désactiver un index cluster permet d'éviter l'accès aux données d’une table. Les données restent dans la table, mais les opérations du langage de manipulation de données (DML) ne peuvent pas les utiliser tant que l'index n'est pas exclu ou recréé.

Limites

L'index n'est pas géré pendant qu'il est désactivé.

L'optimiseur de requêtes ne tient pas compte de l'index désactivé lors de la création de plans d'exécution de requêtes. En outre, les requêtes qui référencent l'index désactivé avec un indicateur de table échouent.

Vous ne pouvez pas créer un index qui utilise le même nom qu'un index désactivé existant.

Un index désactivé peut être supprimé.

Lorsque vous désactivez un index unique, la PRIMARY KEY, la contrainte UNIQUE et toutes les contraintes FOREIGN KEY qui référencent les colonnes d’index des autres tables sont également désactivées. Lorsque vous désactivez un index cluster, toutes les contraintes FOREIGN KEY entrantes et sortantes sur la table sous-jacente sont également désactivées. Les noms des contraintes sont répertoriés dans un message d'avertissement lorsque l'index est désactivé. Une fois l'index recréé, toutes les contraintes doivent être activées manuellement à l'aide de l'instruction ALTER TABLE CHECK CONSTRAINT.

Les index non-cluster sont automatiquement désactivés lorsque l'index cluster associé est désactivé. Elles restent désactivées tant si l'index cluster de la table ou de la vue est activé ou si l'index cluster de la table est 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.

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

La désactivation d'un index cluster sur une table désactive également tous les index cluster et non cluster sur les vues qui font référence à cette table. Ces index doivent être recréés comme ceux de la table référencée.

Les lignes de données de l'index cluster désactivé ne sont accessibles que pour supprimer ou recréer cet index.

Vous pouvez recréer un index non cluster désactivé en ligne, lorsque la table ne possède pas d'index cluster désactivé. Toutefois, vous devez toujours recréer un index cluster désactivé hors ligne, si vous utilisez une instruction ALTER INDEX REBUILD ou CREATE INDEX WITH DROP_EXISTING. Pour plus d’informations sur les opérations sur les index en ligne, consultez Réaliser des opérations sur les index en ligne.

L'instruction CREATE STATISTICS ne peut pas être correctement exécutée sur une table avec 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 les conditions suivantes sont réunies :

  • AUTO_CREATE_STATISTICS est défini sur ON.
  • Il n'existe pas de statistiques sur la colonne.
  • Des statistiques sont requises pendant l'optimisation de la requête.

Si un index cluster est désactivé, DBCC CHECKDB ne peut pas retourner d'informations sur la table sous-jacente. Au lieu de cela, l'instruction signale que l'index cluster est désactivé. DBCC INDEXDEFRAG ne peut pas être utilisé pour défragmenter un index désactivé ; l'instruction échoue avec un message d'erreur. Utilisez l'instruction DBCC DBREINDEX pour recréer un index désactivé.

La création d'un nouvel index cluster active les index non cluster précédemment désactivés. Pour plus d’informations, consultez Enable Indexes and Constraints.

Si la table est un segment de mémoire, tous les index non cluster sont recréés.

Autorisations

L’exécution de ALTER INDEX nécessite au moins une autorisation ALTER sur la table ou la vue.

Utiliser SQL Server Management Studio

Désactiver un index

  1. Dans l'Explorateur d'objets, cliquez sur le signe plus (+) pour élargir la base de données qui contient la table sur laquelle vous souhaitez désactiver un index.

  2. Sélectionnez le signe plus (+) pour développer le dossier Tables.

  3. Cliquez sur le signe plus (+) pour élargir la table sur laquelle vous souhaitez désactiver un index.

  4. Cliquez sur le signe plus (+) pour élargir le dossier Index.

  5. Cliquez avec le bouton droit sur l’index que vous souhaitez désactiver et sélectionnez Désactiver.

    Remarque

    Si la table esto ouverte en mode Création, le contrôle Désactiver n’est pas disponible. Pour continuer, fermez le concepteur de tables et recommencez.

  6. Dans la boîte de dialogue Désactiver des index, vérifiez que l'index correct figure dans la grille Index à désactiver et cliquez sur OK.

Désactiver tous les index d'une table

  1. Dans l'Explorateur d'objets, cliquez sur le signe plus (+) pour élargir la base de données qui contient la table sur laquelle vous souhaitez désactiver les index.

  2. Sélectionnez le signe plus (+) pour développer le dossier Tables.

  3. Cliquez sur le signe plus (+) pour élargir la table sur laquelle vous souhaitez désactiver les index.

  4. Cliquez avec le bouton droit sur le dossier Index et sélectionnez Désactiver tout.

  5. Dans la boîte de dialogue Désactiver des index, vérifiez que les index corrects figurent dans la grille Index à désactiver et cliquez sur OK. Pour supprimer un index de la grille Index à désactiver, sélectionnez-le et appuyez sur la touche Suppr.

Les informations suivantes sont disponibles dans la boîte de dialogue Désactiver des index :

  • Nom de l'index

    Affiche le nom de l'index. Durant l'exécution, cette colonne comporte également une icône pour indiquer l'état.

  • Nom de la table

    Affiche le nom de la table ou de la vue sur laquelle l'index a été créé.

  • Type d'index

    Affiche le type d’index : Cluster, Non-cluster, Spatialou XML.

  • État

    Affiche l'état de l'opération de désactivation. Les valeurs possibles après l'exécution sont les suivantes :

    • Vide

      Avant l’exécution, l’état est vide.

    • En cours

      La désactivation des index a commencé mais elle n'est pas terminée.

    • Réussite

      L'opération de désactivation est achevée.

    • Error

      Une erreur est survenue pendant l’opération de désactivation des index et celle-ci ne s’est pas terminée correctement.

    • Arrêté

      La désactivation de l'index ne s’est pas terminée parce que l'utilisateur a arrêté l'opération.

  • Message

    Test des messages d'erreur survenant durant la désactivation. Pendant l'exécution, les erreurs sont affichées comme des liens hypertexte. Le corps du message d'erreur est indiqué dans le lien hypertexte. La colonne Message est souvent trop étroite pour que la totalité du message soit visible. Deux solutions sont possibles pour afficher l'intégralité du texte :

    • Déplacez le pointeur de la souris sur la cellule du message pour afficher une info-bulle contenant le texte de l'erreur.
    • Cliquez sur le lien hypertexte pour afficher une boîte de dialogue montrant le message d'erreur.

Utiliser Transact-SQL

Cet article requiert l'échantillon de base de données AdventureWorks, que vous pouvez télécharger à partir de la page d'accueil des AdventureWorks2022échantillons et Projects de communautés Microsoft SQL Server.

Désactiver un index

  1. Dans l' Explorateur d'objets, connectez-vous à une instance du Moteur de base de données.

  2. Dans la barre d’outils standard, sélectionnez Nouvelle requête.

  3. Copiez et collez l’exemple suivant dans la fenêtre de requête, puis sélectionnez Exécuter. Cet exemple désactive l’index IX_Employee_OrganizationLevel_OrganizationNode sur la table HumanResources.Employee.

    USE AdventureWorks2022;
GO

ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode
    ON HumanResources.Employee
DISABLE;

Désactiver tous les index d'une table

  1. Dans l' Explorateur d'objets, connectez-vous à une instance du Moteur de base de données.

  2. Dans la barre d’outils standard, sélectionnez Nouvelle requête.

  3. Copiez et collez l’exemple suivant dans la fenêtre de requête, puis sélectionnez Exécuter. Cet exemple désactive tous les index de la table HumanResources.Employee.

    USE AdventureWorks2022;
GO

ALTER INDEX ALL ON HumanResources.Employee
DISABLE;

Contenu connexe