Partager via

Désactiver les index et les contraintes

Cette rubrique explique comment désactiver un index ou des contraintes dans SQL Server 2014 à l’aide de SQL Server Management Studio ou de Transact-SQL. La désactivation d’un index empêche l’accès utilisateur à l’index et pour les index cluster aux données de table sous-jacentes. La définition d’index reste dans les métadonnées et les statistiques d’index sont conservées sur des index non cluster. La désactivation d’un index non clusterisé ou clusterisé sur une vue supprime physiquement les données d’index. La désactivation d’un index cluster sur une table empêche l’accès aux données ; les données restent toujours dans la table, mais elles ne sont pas disponibles pour les opérations de langage de manipulation des données (DML) jusqu’à ce que l’index soit supprimé ou reconstruit.

Dans cette rubrique

Avant de commencer

Limitations et restrictions

  • L’index n’est pas conservé pendant qu’il est désactivé.

  • L’optimiseur de requête ne prend pas en compte 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é.

  • Lors de la désactivation d’un index unique, la contrainte PRIMARY KEY ou UNIQUE et toutes les contraintes FOREIGN KEY qui référencent les colonnes indexées à partir d’autres tables sont également désactivées. Lors de la désactivation d’un index clusterisé, toutes les contraintes FOREIGN KEY entrantes et sortantes sur la table sous-jacente sont également désactivées. Les noms de contraintes sont répertoriés dans un message d’avertissement lorsque l’index est désactivé. Après avoir reconstruit l'index, 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 ne peuvent pas être mis en service tant que l'index clusterisé sur la table ou la vue n'est pas activé ou que l'index clusterisé de la table n'est supprimé. Les index non clusterisés doivent être explicitement activés, sauf si l’index clusterisé a été activé avec l'instruction ALTER INDEX ALL REBUILD.

  • L’instruction ALTER INDEX ALL REBUILD reconstruit et active tous les index désactivés sur la table, à l’exception des index désactivés sur les vues. Les index sur les 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 reconstruits tout comme ceux de la table référencée.

  • Les lignes de données de l’index cluster désactivé ne sont pas accessibles, sauf pour supprimer ou reconstruire l’index cluster.

  • Vous pouvez reconstruire un index non cluster désactivé en ligne lorsque la table n’a pas d’index cluster désactivé. Toutefois, vous devez toujours reconstruire un index cluster désactivé hors connexion 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 Effectuer des opérations d’index en ligne.

  • L’instruction CREATE STATISTICS ne peut pas être exécutée avec succès sur une table qui a 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 existent :

    • AUTO_CREATE_STATISTICS est défini sur ON

    • Il n’existe aucune statistique existante pour la colonne.

    • Les statistiques sont requises lors de l’optimisation des requêtes.

  • 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. Vous pouvez utiliser DBCC DBREINDEX pour reconstruire un index désactivé.

  • La création d’un index cluster active les index non cluster précédemment désactivés. Pour plus d’informations, consultez Activer les index et les contraintes.

Sécurité

Autorisations

Pour exécuter ALTER INDEX, l'autorisation ALTER sur la table ou la vue est requise, au minimum.

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 contenant 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 Indexes.

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

  6. Dans la boîte de dialogue Désactiver les index , vérifiez que l’index correct se trouve dans les index pour désactiver la grille, puis 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 contenant 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 les index, vérifiez que les index appropriés se trouvent dans la grille Index à désactiver, puis cliquez sur OK. Pour supprimer un index de la grille des Index à désactiver, sélectionnez l’index, puis appuyez sur la touche Supprimer.

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

Nom de l’index
Affiche le nom de l’index. Pendant l’exécution, cette colonne affiche également une icône représentant l’état.

Nom du tableau
Affiche le nom de la table ou de la vue sur laquelle l’index a été créé.

Type d’index
Affiche le type de l’index : Clustered, Non clustered, 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 , l’état est vide.

  • En cours

    La désactivation des index a été démarrée, mais n’est pas terminée.

  • Succès

    L’opération de désactivation s’est terminée avec succès.

  • Erreur

    Une erreur s’est produite pendant l’opération de désactivation de l’index et l’opération n’a pas réussi.

  • Arrêté

    La désactivation de l’index n’a pas été effectuée correctement, car l’utilisateur a arrêté l’opération.

Message
Fournit le texte des messages d’erreur pendant l’opération de désactivation. Pendant l’exécution, les erreurs apparaissent sous forme de liens hypertexte. Le texte des liens hypertexte décrit le corps de l’erreur. La colonne Message est rarement suffisamment large pour lire le texte intégral du message. Il existe deux façons d’obtenir le texte intégral :

  • Déplacez le pointeur de la souris sur la cellule de message pour afficher une info-bulle avec le texte d’erreur.

  • Cliquez sur le lien hypertexte pour afficher une boîte de dialogue affichant l’erreur complète.

Utilisation de Transact-SQL

Pour 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, 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).

  • Last updated on