Désactiver les index et contraintes

  • Article

Cette rubrique explique comment désactiver un index ou des contraintes dans SQL Server 2012 à 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. La désactivation d'un index, qu'il soit non cluster ou cluster, sur une vue supprime physiquement les données de l'index. La désactivation d'un index cluster sur une table empêche l'accès aux données de celle-ci ; ces dernières existent toujours dans la table, mais les opérations de langage de manipulation de données (DML) ne peuvent pas les utiliser tant que l'index n'est pas supprimé ou reconstruit.

Dans cette rubrique

  • Avant de commencer :

    Limitations et restrictions

    Sécurité

  • Pour désactiver un index, utilisez :

    SQL Server Management Studio

    Transact-SQL

Avant de commencer

Limitations et restrictions

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

  • L'optimiseur de requête ne tient pas compte de l'index désactivé lors de la création de plans d'exécution de requête. 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 contrainte PRIMARY KEY ou UNIQUE et toutes les contraintes FOREIGN KEY qui référencent les colonnes indexées 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 reconstruit, 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é. 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.

  • 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 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 appartenant à la table référencée.

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

  • 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écuter des opérations en ligne sur les index.

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

  • Si un index cluster est désactivé, DBCC CHECKDB ne peut pas retourner d'informations sur la table sous-jacente. À la place, 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. Vous pouvez utiliser 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 Activer les index et contraintes.

Sécurité

Autorisations

Pour pouvoir exécuter l'instruction ALTER INDEX, vous devez obligatoirement bénéficier au minimum d'autorisations nécessaires pour exécuter les instructions ALTER sur la table ou la vue.

Icône de flèche utilisée avec le lien Retour en haut[Haut de la page]

Utilisation de SQL Server Management Studio

Pour désactiver un index

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

  2. Cliquez sur le signe plus (+) pour développer le dossier Tables.

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

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

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

  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.

Pour désactiver tous les index d'une table

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

  2. Cliquez sur le signe plus (+) pour développer le dossier Tables.

  3. Cliquez sur le signe plus (+) pour développer 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, Spatial ou 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, la valeur d'État est vide.

    • Opération en cours

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

    • Réussite

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

    • Erreur

      Une erreur est survenue pendant la désactivation d'index et celle-ci n'a pas pu être accomplie.

    • Arrêté

      La désactivation d'index n'a pas été accomplie parce que l'utilisateur a interrompu 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 indiquant le message d'erreur.

Icône de flèche utilisée avec le lien Retour en haut[Haut de la page]

Utilisation de Transact-SQL

Pour désactiver un index

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

  2. Dans la barre d'outils standard, cliquez sur Nouvelle requête.

  3. Copiez et collez l'exemple suivant dans la fenêtre de requête, puis cliquez sur Exécuter.

    USE AdventureWorks2012;
GO
-- disables the IX_Employee_OrganizationLevel_OrganizationNode index
-- on the HumanResources.Employee table
ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee
DISABLE;

Pour 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, cliquez sur Nouvelle requête.

  3. Copiez et collez l'exemple suivant dans la fenêtre de requête, puis cliquez sur Exécuter.

    USE AdventureWorks2012;
GO
-- Disables all indexes on the HumanResources.Employee table.
ALTER INDEX ALL ON HumanResources.Employee
DISABLE;

Pour plus d'informations, consultez ALTER INDEX (Transact-SQL).

Icône de flèche utilisée avec le lien Retour en haut[Haut de la page]