Activer des index et des contraintes

  • Article

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

Cette rubrique explique comment activer un index désactivé dans SQL Server à l’aide de SQL Server Management Studio ou de Transact-SQL. Lorsqu'un index est désactivé, il reste à l'état désactivé tant qu'il n'est pas reconstruit ou supprimé

Dans cette rubrique

Avant de commencer

Limitations et restrictions

  • Après la reconstruction de l'index, toutes les contraintes qui étaient désactivées du fait de la désactivation de l'index doivent être réactivées manuellement. Les contraintes PRIMARY KEY et UNIQUE sont activées par la reconstruction de l'index associé. Cet index doit être reconstruit (activé) avant que les contraintes FOREIGN KEY qui font référence à la contrainte PRIMARY KEY ou UNIQUE puissent être activées. Les contraintes FOREIGN KEY sont activées à l'aide de l'instruction ALTER TABLE CHECK CONSTRAINT.

  • La reconstruction d'un index cluster désactivé n'est pas possible lorsque l'option ONLINE a la valeur ON.

  • Lorsque l'index cluster est désactivé ou activé et que l'index non-cluster est désactivé, l'action sur l'index cluster produit les résultats ci-dessous sur l'index non-cluster désactivé.

    Action sur l'index cluster Index non cluster désactivé…
    ALTER INDEX REBUILD Reste désactivé.
    ALTER INDEX ALL REBUILD Est reconstruit et activé.
    DROP INDEX Reste désactivé.
    CREATE INDEX WITH DROP_EXISTING Reste désactivé.

    La création d'un nouvel index cluster suit le même comportement que ALTER INDEX ALL REBUILD.

  • Les actions autorisées sur les index non-cluster associés à un index cluster dépendent de l'état, désactivé ou activé, des deux types d'index. Le tableau ci-dessous récapitule les actions autorisées sur les index non-cluster.

    Action sur l'index non cluster Lorsque les index cluster et non cluster sont désactivés. Lorsque l'index cluster est activé et que l'index non cluster est activé ou désactivé.
    ALTER INDEX REBUILD L'action échoue. L'action réussit.
    DROP INDEX L'action réussit. L'action réussit.
    CREATE INDEX WITH DROP_EXISTING L'action échoue. L'action réussit.

  • Lors de la regénération d’index non-cluster compressés désactivés, data_compression est défini sur 'none' par défaut, ce qui signifie que les index seront décompressés. Cela est dû à la perte des métadonnées des paramètres de compression quand les index non-cluster sont désactivés. Pour contourner ce problème, spécifiez la compression de données explicite dans une instruction rebuild.

Sécurité

Autorisations

Nécessite une autorisation ALTER sur la table ou la vue. S’il utilise DBCC DBREINDEX, l’utilisateur doit être propriétaire de la table ou être membre du rôle serveur fixe sysadmin ou des rôles de base de données fixes db_ddladmin et db_owner.

Utilisation de SQL Server Management Studio

Pour activer un index désactivé

  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 activer 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 activer 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 activer et sélectionnez Reconstruire.

  6. Dans la boîte de dialogue Reconstruire les index , vérifiez que l'index correct figure dans la grille Index à reconstruire , puis cliquez sur OK.

Pour activer 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 activer 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 activer les index.

  4. Cliquez avec le bouton droit sur le dossier Index et sélectionnez Tout reconstruire.

  5. Dans la boîte de dialogue Reconstruire les index , vérifiez que les index corrects figurent dans la grille Index à reconstruire , puis cliquez sur OK. Pour supprimer un index de la grille Index à reconstruire , sélectionnez l'index et appuyez sur la touche SUPPR.

Les informations suivantes sont disponibles dans la boîte de dialogue Reconstruire les index :

Utilisation de Transact-SQL

Pour activer un index désactivé à l'aide de ALTER 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 AdventureWorks2022;  
GO  
-- Enables the IX_Employee_OrganizationLevel_OrganizationNode index  
-- on the HumanResources.Employee table.  

ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee  
REBUILD;   
GO

Pour activer un index désactivé à l'aide de CREATE 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 AdventureWorks2022;  
GO  
-- re-creates the IX_Employee_OrganizationLevel_OrganizationNode index  
-- on the HumanResources.Employee table  
-- using the OrganizationLevel and OrganizationNode columns  
-- and then deletes the existing IX_Employee_OrganizationLevel_OrganizationNode index  
CREATE INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee  
   (OrganizationLevel, OrganizationNode)  
WITH (DROP_EXISTING = ON);  
GO

Pour activer un index désactivé à l'aide de DBCC DBREINDEX

  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 AdventureWorks2022;   
GO  
-- enables the IX_Employee_OrganizationLevel_OrganizationNode index  
-- on the HumanResources.Employee table  
DBCC DBREINDEX ("HumanResources.Employee", IX_Employee_OrganizationLevel_OrganizationNode);  
GO

Pour activer tous les index d'une table à l'aide de ALTER 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 AdventureWorks2022;  
GO  
-- enables all indexes  
-- on the HumanResources.Employee table  
ALTER INDEX ALL ON HumanResources.Employee  
REBUILD;  
GO

Pour activer tous les index d'une table à l'aide de DBCC DBREINDEX

  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 AdventureWorks2022;   
GO  
-- enables all indexes  
-- on the HumanResources.Employee table  
DBCC DBREINDEX ("HumanResources.Employee", " ");  
GO

Pour plus d’informations, consultez ALTER INDEX (Transact-SQL), CREATE INDEX (Transact-SQL) et DBCC DBREINDEX (Transact-SQL).