Désactiver les index et contraintesDisable Indexes and Constraints

CETTE RUBRIQUE S’APPLIQUE À : ouiSQL Server (à partir de la version 2016)ouiAzure SQL DatabasenonAzure SQL Data WarehousenonParallel Data Warehouse THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Cette rubrique explique comment désactiver un index ou des contraintes dans SQL Server 2016SQL Server 2016 à l'aide de SQL Server Management StudioSQL Server Management Studio ou de Transact-SQLTransact-SQL.This topic describes how to disable an index or constraints in SQL Server 2016SQL Server 2016 by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-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.Disabling an index prevents user access to the index, and for clustered indexes to the underlying table data. 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.The index definition remains in metadata, and index statistics are kept on nonclustered indexes. La désactivation d'un index, qu'il soit non cluster ou cluster, sur une vue supprime physiquement les données de l'index.Disabling a nonclustered or clustered index on a view physically deletes the index data. 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.Disabling a clustered index on a table prevents access to the data; the data still remains in the table, but is unavailable for data manipulation language (DML) operations until the index is dropped or rebuilt.

Dans cette rubriqueIn This Topic

Avant de commencer Before You Begin

Limitations et restrictions Limitations and Restrictions

  • L'index n'est pas géré pendant qu'il est désactivé.The index is not maintained while it is disabled.

  • 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.The query optimizer does not consider the disabled index when creating query execution plans. En outre, les requêtes qui référencent l'index désactivé avec un indicateur de table échouent.Also, queries that reference the disabled index with a table hint fail.

  • Vous ne pouvez pas créer un index qui utilise le même nom qu'un index désactivé existant.You cannot create an index that uses the same name as an existing disabled index.

  • Un index désactivé peut être supprimé.A disabled index can be dropped.

  • 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.When disabling a unique index, the PRIMARY KEY or UNIQUE constraint and all FOREIGN KEY constraints that reference the indexed columns from other tables are also disabled. 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.When disabling a clustered index, all incoming and outgoing FOREIGN KEY constraints on the underlying table are also disabled. Les noms des contraintes sont répertoriés dans un message d'avertissement lorsque l'index est désactivé.The constraint names are listed in a warning message when the index is disabled. Une fois l'index reconstruit, toutes les contraintes doivent être activées manuellement à l'aide de l'instruction ALTER TABLE CHECK CONSTRAINT.After rebuilding the index, all constraints must be manually enabled by using the ALTER TABLE CHECK CONSTRAINT statement.

  • Les index non-cluster sont automatiquement désactivés lorsque l'index cluster associé est désactivé.Nonclustered indexes are automatically disabled when the associated clustered index is disabled. 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é.They cannot be enabled until either the clustered index on the table or view is enabled or the clustered index on the table is dropped. 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.Nonclustered indexes must be explicitly enabled, unless the clustered index was enabled by using the ALTER INDEX ALL REBUILD statement.

  • 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.The ALTER INDEX ALL REBUILD statement rebuilds and enables all disabled indexes on the table, except for disabled indexes on views. Les index sur des vues doivent être activés dans une instruction ALTER INDEX ALL REBUILD distincte.Indexes on views must be enabled in a separate ALTER INDEX ALL REBUILD statement.

  • 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.Disabling a clustered index on a table also disables all clustered and nonclustered indexes on views that reference that table. Ces index doivent être recréés comme ceux appartenant à la table référencée.These indexes must be rebuilt just as those on the referenced table.

  • Les lignes de données de l'index cluster désactivé ne sont accessibles que pour supprimer ou reconstruire cet index.The data rows of the disabled clustered index cannot be accessed except to drop or rebuild the clustered index.

  • Vous pouvez reconstruire un index non cluster désactivé en ligne lorsque la table ne possède pas d'index cluster désactivé.You can rebuild a disabled nonclustered index online when the table does not have a disabled clustered index. 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.However, you must always rebuild a disabled clustered index offline if you use either the ALTER INDEX REBUILD or CREATE INDEX WITH DROP_EXISTING statement. Pour plus d’informations sur les opérations en ligne sur les index, consultez Exécuter des opérations en ligne sur les index.For more information about online index operations, see Perform Index Operations Online.

  • L'instruction CREATE STATISTICS ne peut pas être correctement exécutée sur une table qui possède un index cluster désactivé.The CREATE STATISTICS statement cannot be successfully executed on a table that has a disabled clustered index.

  • 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 :The AUTO_CREATE_STATISTICS database option creates new statistics on a column when the index is disabled and the following conditions exist:

    • AUTO_CREATE_STATISTICS a pour valeur ON.AUTO_CREATE_STATISTICS is set to ON

    • Il n'existe pas de statistiques sur la colonne.There are no existing statistics for the column.

    • Des statistiques sont requises pendant l'optimisation de la requête.Statistics are required during query optimization.

  • 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é.If a clustered index is disabled, DBCC CHECKDB cannot return information about the underlying table; instead, the statement reports that the clustered index is disabled. DBCC INDEXDEFRAG ne peut pas être utilisé pour défragmenter un index désactivé ; l'instruction échoue avec un message d'erreur.DBCC INDEXDEFRAG cannot be used to defragment a disabled index; the statement fails with an error message. Utilisez l'instruction DBCC DBREINDEX pour recréer un index désactivé.You can use DBCC DBREINDEX to rebuild a disabled index.

  • La création d'un nouvel index cluster active les index non cluster précédemment désactivés.Creating a new clustered index enables previously disabled nonclustered indexes. Pour plus d’informations, consultez Enable Indexes and Constraints.For more information, see Enable Indexes and Constraints.

Sécurité Security

Autorisations Permissions

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.To execute ALTER INDEX, at a minimum, ALTER permission on the table or view is required.

Utilisation de SQL Server Management Studio Using SQL Server Management Studio

Pour désactiver un indexTo disable an 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.In Object Explorer, click the plus sign to expand the database that contains the table on which you want to disable an index.

  2. Cliquez sur le signe plus (+) pour développer le dossier Tables .Click the plus sign to expand the Tables folder.

  3. Cliquez sur le signe plus (+) pour développer la table sur laquelle vous souhaitez désactiver un index.Click the plus sign to expand the table on which you want to disable an index.

  4. Cliquez sur le signe plus (+) pour développer le dossier Index .Click the plus sign to expand the Indexes folder.

  5. Cliquez avec le bouton droit sur l’index que vous souhaitez désactiver et sélectionnez Désactiver.Right-click the index you want to disable and select Disable.

  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.In the Disable Indexes dialog box, verify that the correct index is in the Indexes to disable grid and click OK.

Pour désactiver tous les index d'une tableTo disable all indexes on a 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.In Object Explorer, click the plus sign to expand the database that contains the table on which you want to disable the indexes.

  2. Cliquez sur le signe plus (+) pour développer le dossier Tables .Click the plus sign to expand the Tables folder.

  3. Cliquez sur le signe plus (+) pour développer la table sur laquelle vous souhaitez désactiver les index.Click the plus sign to expand the table on which you want to disable the indexes.

  4. Cliquez avec le bouton droit sur le dossier Index et sélectionnez Désactiver tout.Right-click the Indexes folder and select Disable All.

  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.In the Disable Indexes dialog box, verify that the correct indexes are in the Indexes to disable grid and click OK. Pour supprimer un index de la grille Index à désactiver , sélectionnez-le et appuyez sur la touche SUPPR.To remove an index from the Indexes to disable grid, select the index and then press the Delete key.

    Les informations suivantes sont disponibles dans la boîte de dialogue Désactiver des index :The following information is available in the Disable Indexes dialog box:

    Nom de l'indexIndex Name
    Affiche le nom de l'index.Displays the name of the index. Durant l'exécution, cette colonne comporte également une icône pour indiquer l'état.During execution, this column also displays an icon representing the status.

    Nom de la tableTable Name
    Affiche le nom de la table ou de la vue sur laquelle l'index a été créé.Displays the name of the table or view that the index was created on.

    Type d'indexIndex Type
    Affiche le type d’index : Cluster, Non-cluster, Spatialou XML.Displays the type of the index: Clustered, Nonclustered, Spatial, or XML.

    ÉtatStatus
    Affiche l'état de l'opération de désactivation.Displays the status of the disable operation. Les valeurs possibles après l'exécution sont les suivantes :Possible values after execution are:

  • VideBlank

    Avant l'exécution, la valeur d' État est vide.Prior to execution Status is blank.

  • En coursIn progress

    La désactivation d'index a débuté mais elle n'est pas terminée.Disabling of the indexes has been started but is not complete.

  • RéussiSuccess

    L'opération de désactivation est achevée.The disable operation completed successfully.

  • ErreurError

    Une erreur est survenue pendant la désactivation d'index et celle-ci n'a pas pu être accomplie.An error was encountered during the index disable operation, and the operation did not complete successfully.

  • ArrêtéStopped

    La désactivation d'index n'a pas été accomplie parce que l'utilisateur a interrompu l'opération.The disable of the index was not completed successfully because the user stopped the operation.

    MessageMessage
    Test des messages d'erreur survenant durant la désactivation.Provides the text of error messages during the disable operation. Pendant l'exécution, les erreurs sont affichées comme des liens hypertexte.During execution, errors appear as hyperlinks. Le corps du message d'erreur est indiqué dans le lien hypertexte.The text of the hyperlinks describes the body of the error. La colonne Message est souvent trop étroite pour que la totalité du message soit visible.The Message column is rarely wide enough to read the full message text. Deux solutions sont possibles pour afficher l'intégralité du texte :There are two ways to get the full text:

  • Déplacez le pointeur de la souris sur la cellule du message pour afficher une info-bulle contenant le texte de l'erreur.Move the mouse pointer over the message cell to display a ToolTip with the error text.

  • Cliquez sur le lien hypertexte pour afficher une boîte de dialogue indiquant le message d'erreur.Click the hyperlink to display a dialog box displaying the full error.

Utilisation de Transact-SQL Using Transact-SQL

Pour désactiver un indexTo disable an index

  1. Dans l' Explorateur d'objets, connectez-vous à une instance du Moteur de base de donnéesDatabase Engine.In Object Explorer, connect to an instance of Moteur de base de donnéesDatabase Engine.

  2. Dans la barre d'outils standard, cliquez sur Nouvelle requête.On the Standard bar, click New Query.

  3. Copiez et collez l'exemple suivant dans la fenêtre de requête, puis cliquez sur Exécuter.Copy and paste the following example into the query window and click Execute.

    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 tableTo disable all indexes on a table

  1. Dans l' Explorateur d'objets, connectez-vous à une instance du Moteur de base de donnéesDatabase Engine.In Object Explorer, connect to an instance of Moteur de base de donnéesDatabase Engine.

  2. Dans la barre d'outils standard, cliquez sur Nouvelle requête.On the Standard bar, click New Query.

  3. Copiez et collez l'exemple suivant dans la fenêtre de requête, puis cliquez sur Exécuter.Copy and paste the following example into the query window and click Execute.

    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).For more information, see ALTER INDEX (Transact-SQL).