DROP INDEX (Transact-SQL)

L'instruction DROP INDEX supprime des index de la base de données SQL Server active. Pour supprimer un index à l'aide de l'Explorateur d'objets, consultez Procédure : supprimer un index (SQL Server Management Studio).

Vous ne pouvez pas utiliser l'instruction DROP INDEX pour supprimer un index ayant une contrainte PRIMARY KEY ou UNIQUE. Pour supprimer la contrainte puis l'index, utilisez ALTER TABLE avec la clause DROP CONSTRAINT.

Pour utiliser l'instruction DROP INDEX pour supprimer un index cluster et déplacer la table résultante vers un autre groupe de fichiers ou schéma de partition dans une transaction unique, spécifiez l'option MOVE TO.

Important

La syntaxe définie dans <drop_backward_compatible_index> sera supprimée dans une version future de SQL Server. Évitez d'utiliser cette syntaxe pour supprimer des index dans tout nouveau travail de développement et envisagez de modifier les applications qui l'utilisent actuellement. Utilisez plutôt la syntaxe spécifiée sous <drop_relational_or_xml_index>. Vous ne pouvez pas supprimer d'index XML à l'aide d'une syntaxe à compatibilité descendante.

Conventions de syntaxe Transact-SQL

Syntaxe

DROP INDEX
{ <drop_relational_or_xml_or_spatial_index> [ ,...n ] 
| <drop_backward_compatible_index> [ ,...n ]
}

<drop_relational_or_xml_or_spatial_index> ::=
        index_name ON <object> 
    [ WITH ( <drop_clustered_index_option> [ ,...n ] ) ]

<drop_backward_compatible_index> ::=
    [ owner_name. ] table_or_view_name.index_name

<object> ::=
{
    [ database_name. [ schema_name ] . | schema_name. ] 
        table_or_view_name
}

<drop_clustered_index_option> ::=
{
    MAXDOP = max_degree_of_parallelism
    | ONLINE = { ON | OFF }
  | MOVE TO { partition_scheme_name ( column_name ) 
            | filegroup_name
            | "default" 
            }
  [ FILESTREAM_ON { partition_scheme_name 
            | filestream_filegroup_name 
            | "default" } ]
}

Arguments

• index_name
  Nom de l'index à supprimer.

• database_name
  Nom de la base de données.

• schema_name
  Nom du schéma auquel appartient la table ou la vue.

• table_or_view_name
  Nom de la table ou de la vue associée à l'index. Les index spatiaux sont pris en charge uniquement sur les tables.

  Pour afficher un rapport des index relatifs à un objet, utilisez l'affichage catalogue sys.indexes.

• <drop_clustered_index_option>
  Contrôle les options d'index cluster. Ces options ne peuvent pas être utilisées avec d'autres types d'index.

• MAXDOP = max_degree_of_parallelism
  Substitue l'option de configuration max degree of parallelism pendant la durée de l'opération d'indexation. Pour plus d'informations, consultez Option Degré maximum de parallélisme. Utilisez MAXDOP pour limiter le nombre de processeurs utilisés dans une exécution de plans parallèles. Le nombre maximal de processeurs est de 64.

  Important

  MAXDOP n'est pas autorisé pour les index XML ou spatiaux.

  Valeurs possibles de max_degree_of_parallelism :

  • 1
    Supprime la création du plan d'exécution parallèle.

  • >1
    Limite au nombre spécifié le nombre maximal de processeurs utilisés dans les opérations d'index parallèles.

  • 0 (valeur par défaut)
    Utilise le nombre réel de processeurs ou un nombre de processeurs inférieur en fonction de la charge de travail actuelle du système.

  Pour plus d'informations, consultez Configuration d'opérations d'index parallèles.

  [!REMARQUE]

  Les opérations d'index parallèles ne sont disponibles que dans les éditions Enterprise, Developer et Evaluation de SQL Server.

• ONLINE = ON | OFF
  Spécifie si les tables sous-jacentes et les index associés sont disponibles pour modifier des requêtes et des données pendant l'opération d'index. La valeur par défaut est OFF.

  • ON
    Les verrous de table à long terme ne sont pas maintenus. Cela permet aux requêtes ou mises à jour de la table sous-jacente de continuer.

  • OFF
    Les verrous de table sont appliqués et la table n'est pas disponible pendant la durée de l'opération d'index.

  L'option ONLINE ne peut être spécifiée que lorsque vous supprimez des index cluster. Pour plus d'informations, consultez la section Remarques.

  [!REMARQUE]

  Les opérations d'index en ligne ne sont disponibles que dans les éditions Enterprise, Developer et Evaluation de SQL Server.

• MOVE TO { partition_scheme_name**(column_name)** | filegroup_name | "default"
  Spécifie un emplacement pour déplacer les lignes de données qui se trouvent actuellement au niveau feuille de l'index cluster. Les données sont déplacées vers le nouvel emplacement sous la forme d'un segment de mémoire. Vous pouvez spécifier un schéma de partition ou un groupe de fichiers déjà existants comme nouvel emplacement. MOVE TO n'est pas valide pour les vues non indexées ou les index non cluster. Si aucun schéma de partition ou groupe de fichiers n'est spécifié, la table résultante sera située sur le même schéma de partition ou groupe de fichiers que celui défini pour l'index cluster.

  Si un index cluster est supprimé à l'aide de MOVE TO, tous les index non cluster sur la table de base sont recréés, mais ils restent dans leur schéma de partition ou groupe de fichiers d'origine. Si la table de base est déplacée vers un schéma de partition ou groupe de fichiers différent, les index non cluster ne sont pas déplacés pour coïncider avec le nouvel emplacement de la table de base (segment de mémoire). Par conséquent, même si les index non cluster étaient précédemment alignés avec l'index cluster, ils peuvent ne plus être alignés avec le segment de mémoire. Pour plus d'informations sur l'alignement des index partitionnés, consultez Consignes spéciales pour les index partitionnés.

  • partition_scheme_name**(column_name)**
    Spécifie un schéma de partition comme emplacement de la table résultante. Le schéma de partition doit déjà avoir été créé en exécutant soit CREATE PARTITION SCHEME, soit ALTER PARTITION SCHEME. Si aucun emplacement n'est spécifié et que la table est partitionnée, la table est incluse dans le même schéma de partition que l'index cluster existant.

    Le nom de la colonne dans le schéma n'est pas limité aux colonnes dans la définition d'index. Toute colonne dans la table de base peut être spécifiée.

  • filegroup_name
    Spécifie un groupe de fichiers comme emplacement de la table résultante. Si aucun emplacement n'est spécifié et que la table n'est pas partitionnée, la table résultante est incluse dans le même groupe de fichiers que l'index cluster. Le groupe de fichiers doit déjà exister.

  • "default"
    Spécifie l'emplacement par défaut de la table résultante.

    [!REMARQUE]

    L'élément default n'est pas un mot clé dans ce contexte. Il s'agit d'un identifiant du groupe de fichiers par défaut qui doit être délimité, comme dans MOVE TO "default" ou MOVE TO [default]. Si "default" est spécifié, l'option QUOTED_IDENTIFIER doit être activée (ON) pour la session active. Il s'agit du paramètre par défaut. Pour plus d'informations, consultez SET QUOTED_IDENTIFIER (Transact-SQL).

• FILESTREAM_ON { partition_scheme_name | filestream_filegroup_name | "default" }
  Spécifie un emplacement pour déplacer la table FILESTREAM qui se trouve actuellement au niveau feuille de l'index cluster. Les données sont déplacées vers le nouvel emplacement sous la forme d'un segment de mémoire. Vous pouvez spécifier un schéma de partition ou un groupe de fichiers déjà existants comme nouvel emplacement. FILESTREAM ON n'est pas valide pour les vues indexées ou les index non cluster. Si aucun schéma de partition n'est spécifié, les données sont placées dans le même schéma de partition que celui qui a été défini pour l'index cluster.

  • partition_scheme_name
    Spécifie un schéma de partition pour les données FILESTREAM. Le schéma de partition doit déjà avoir été créé en exécutant soit CREATE PARTITION SCHEME, soit ALTER PARTITION SCHEME. Si aucun emplacement n'est spécifié et que la table est partitionnée, la table est incluse dans le même schéma de partition que l'index cluster existant.

    Si vous spécifiez un schéma de partition pour MOVE TO, vous devez utiliser le même schéma de partition pour FILESTREAM ON.

  • filestream_filegroup_name
    Spécifie un groupe de fichiers FILESTREAM pour les données FILESTREAM. Si aucun emplacement n'est défini et que la table n'est pas partitionnée, les données sont incluses dans le groupe de fichiers FILESTREAM par défaut.

  • "default"
    Spécifie l'emplacement par défaut des données FILESTREAM.

    [!REMARQUE]

    L'élément default n'est pas un mot clé dans ce contexte. Il s'agit d'un identifiant du groupe de fichiers par défaut qui doit être délimité, comme dans MOVE TO "default" ou MOVE TO [default]. Si "default" est spécifié, l'option QUOTED_IDENTIFIER doit être ON pour la session active. Il s'agit du paramètre par défaut. Pour plus d'informations, consultez SET QUOTED_IDENTIFIER (Transact-SQL).

Notes

Lorsqu'un index non cluster est supprimé, la définition d'index est supprimée des métadonnées et les pages de données d'index (arbre B (B-tree)) sont supprimées des fichiers de base de données. Lorsqu'un index cluster est supprimé, la définition d'index est supprimée des métadonnées et les lignes de données qui étaient stockées au niveau feuille de l'index cluster sont stockées dans la table non triée résultante, un segment de mémoire. Tout l'espace précédemment occupé par l'index est récupéré. Cet espace peut ensuite être réaffecté à n'importe quel objet de la base de données.

Un index ne peut pas être supprimé si le groupe de fichiers dans lequel il se trouve est hors connexion ou défini comme étant en lecture seule.

Lorsque l'index cluster d'une vue indexée est supprimé, tous les index non cluster et les statistiques créées automatiquement sur la même vue sont automatiquement supprimés. Les statistiques créées manuellement ne sont pas supprimées.

La syntaxetable_or_view_name**.**index_name est conservée à des fins de compatibilité descendante. Un index XML ou index spatial ne peut pas être supprimé à l'aide de la syntaxe à compatibilité descendante.

Lors de la suppression d'index contenant au moins 128 étendues, le moteur de base de données diffère les désallocations de pages ainsi que les verrous qui y sont associés jusqu'à ce que la transaction soit validée. Pour plus d'informations, consultez Suppression et reconstruction d'objets volumineux.

Des index peuvent parfois être supprimés et recréés pour réorganiser ou reconstruire l'index, par exemple pour appliquer un nouveau taux de remplissage ou pour réorganiser les données après un chargement en masse. Pour ce faire, l'utilisation de ALTER INDEX est plus efficace, en particulier pour les index cluster. ALTER INDEX REBUILD possède des optimisations permettant d'éviter la surcharge liée à la reconstruction des index non cluster.

Utilisations d'options avec DROP INDEX

Vous pouvez définir les options d'index suivantes lorsque vous supprimez un index cluster : MAXDOP, ONLINE et MOVE TO.

Utilisez MOVE TO pour supprimer l'index cluster et déplacer la table résultante vers un autre groupe de fichiers ou schéma de partition dans une transaction unique.

Lorsque vous spécifiez ONLINE = ON, les requêtes et modifications portant sur les données sous-jacentes et index non cluster associés ne sont pas bloqués par la transaction DROP INDEX. Vous pouvez modifier un seul index cluster en ligne à la fois. Pour une description complète de l'option ONLINE, consultez CREATE INDEX (Transact-SQL).

Vous ne pouvez pas supprimer un index cluster en ligne s'il est désactivé sur une vue ou contient les colonnes text, ntext, image, varchar(max), nvarchar(max), varbinary(max) ou xml dans les lignes de données de niveau feuille.

L'utilisation des options ONLINE = ON et MOVE TO nécessite une espace disque temporaire supplémentaire. Pour plus d'informations, consultez Détermination de l'espace disque requis par les index.

Après la suppression d'un index, le segment de mémoire résultant apparaît dans l'affichage catalogue sys.indexes avec la valeur NULL dans la colonne name. Pour afficher le nom de la table, joignez sys.indexes à sys.tables sur object_id. Pour un exemple de requête, reportez-vous à l'exemple D.

Sur les ordinateurs multiprocesseurs qui exécutent SQL Server 2005 Enterprise Edition ou version ultérieure, DROP INDEX peut, à l'instar d'autres requêtes, utiliser davantage de processeurs pour réaliser les opérations d'analyse et de tri associées à la suppression de l'index cluster. Vous pouvez configurer manuellement le nombre de processeurs utilisés pour exécuter l'instruction DROP INDEX en spécifiant l'option d'index MAXDOP. Pour plus d'informations, consultez Configuration d'opérations d'index parallèles.

Lorsqu'un index cluster est supprimé, les partitions des segments de mémoire correspondants conservent leur paramètre de compression des données, à moins que le schéma de partitionnement soit modifié. Si le schéma de partitionnement est modifié, toutes les partitions sont reconstruites dans un état non compressé (DATA_COMPRESSION = NONE). Pour supprimer un index cluster et modifier le schéma de partitionnement, vous devez effectuer les deux opérations suivantes :

1. supprimer l'index cluster ;

2. modifier la table à l'aide d'une option ALTER TABLE ... REBUILD ... spécifiant l'option de compression.

Lorsqu'un index cluster est supprimé HORS CONNEXION, seuls les niveaux supérieurs des index cluster sont supprimés ; cette opération est donc très rapide. Lorsqu'un index cluster est supprimé EN LIGNE, SQL Server reconstruit le segment de mémoire deux fois, une fois pour l'étape 1 et une fois pour l'étape 2. Pour plus d'informations sur la compression de données, consultez Création de tables et d'index compressés.

Index XML

Les options ne peuvent pas être spécifiées lorsque vous supprimez unindex XML. De plus, vous ne pouvez pas utiliser la syntaxe table_or_view_name**.**index_name. Lorsqu'un index XML primaire est supprimé, tous les index XML secondaires associés sont également supprimés. Pour plus d'informations, consultez Index sur les colonnes de type de données XML.

Index spatiaux

Les index spatiaux sont pris en charge uniquement sur les tables. Lorsque vous supprimez un index spatial, vous ne pouvez pas spécifier d'options ni utiliser **.**index_name. La syntaxe correcte est la suivante :

DROP INDEX spatial_index_name ON spatial_table_name;

Pour plus d'informations sur les index spatiaux, consultez Utilisation d'index spatiaux (moteur de base de données).

Autorisations

L'exécution de DROP INDEX nécessite au moins une autorisation ALTER sur la table ou la vue. L'autorisation est accordée par défaut au rôle serveur fixe sysadmin et aux rôles de base de données fixes db_ddladmin et db_owner.

Exemples

A. Suppression d'un index

L'exemple suivant supprime l'index IX_ProductVendor_VendorID sur la table ProductVendor.

USE AdventureWorks;
GO
DROP INDEX IX_ProductVendor_VendorID 
    ON Purchasing.ProductVendor;
GO

B. Suppression de plusieurs index

L'exemple suivant supprime deux index en une seule transaction.

USE AdventureWorks;
GO
DROP INDEX
    IX_PurchaseOrderHeader_EmployeeID ON Purchasing.PurchaseOrderHeader,
    IX_VendorAddress_AddressID ON Purchasing.VendorAddress;
GO

C. Suppression d'un index cluster en ligne et configuration de l'option MAXDOP

L'exemple suivant supprime un index cluster en affectant à l'option ONLINE la valeur ON et à MAXDOP la valeur 8. L'option MOVE TO n'étant pas spécifiée, la table résultante est stockée dans le même groupe de fichiers que l'index.

[!REMARQUE]

Cet exemple ne peut être exécuté que dans SQL Server 2005 Enterprise Edition ou version ultérieure.

USE AdventureWorks;
GO
DROP INDEX AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate 
    ON Production.BillOfMaterials WITH (ONLINE = ON, MAXDOP = 2);
GO

D. Suppression d'un index cluster en ligne et déplacement de la table vers un nouveau groupe de fichiers

L'exemple suivant supprime un index cluster en ligne et déplace la table résultante (segment de mémoire) vers le groupe de fichiers NewGroup en utilisant la clause MOVE TO. Les affichages catalogue sys.indexes, sys.tables et sys.filegroups sont interrogés pour vérifier le placement de l'index et de la table dans les groupes de fichiers avant et après l'opération de déplacement.

USE AdventureWorks;
GO
--Create a clustered index on the PRIMARY filegroup if it does not exist.
IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = 
            N'AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate')
    CREATE UNIQUE CLUSTERED INDEX
        AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate 
    ON Production.BillOfMaterials (ProductAssemblyID, ComponentID, 
        StartDate)
    ON 'PRIMARY';
GO
-- Verify filegroup location of the clustered index.
SELECT t.name AS [Table Name], i.name AS [Index Name], i.type_desc,
    i.data_space_id, f.name AS [Filegroup Name]
FROM sys.indexes AS i
    JOIN sys.filegroups AS f ON i.data_space_id = f.data_space_id
    JOIN sys.tables as t ON i.object_id = t.object_id
        AND i.object_id = OBJECT_ID(N'Production.BillOfMaterials','U')
GO
--Create filegroup NewGroup if it does not exist.
-- Get the SQL Server data path
DECLARE @data_path nvarchar(256);
SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
                  FROM master.sys.master_files
                  WHERE database_id = 1 AND file_id = 1);

-- execute the ALTER DATABASE statement 
IF NOT EXISTS (SELECT name FROM sys.filegroups
                WHERE name = N'NewGroup')
    BEGIN
    ALTER DATABASE AdventureWorks
        ADD FILEGROUP NewGroup;
    EXECUTE ('ALTER DATABASE AdventureWorks
        ADD FILE (NAME = File1,
            FILENAME = '''+ @data_path + 'File1.ndf'')
        TO FILEGROUP NewGroup');
    END
GO
--Verify new filegroup
SELECT * from sys.filegroups;
GO
-- Drop the clustered index and move the BillOfMaterials table to
-- the Newgroup filegroup.
-- Set ONLINE = OFF to execute this example on editions other than Enterprise Edition.
DROP INDEX AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate 
    ON Production.BillOfMaterials 
    WITH (ONLINE = ON, MOVE TO NewGroup);
GO
-- Verify filegroup location of the moved table.
SELECT t.name AS [Table Name], i.name AS [Index Name], i.type_desc,
    i.data_space_id, f.name AS [Filegroup Name]
FROM sys.indexes AS i
    JOIN sys.filegroups AS f ON i.data_space_id = f.data_space_id
    JOIN sys.tables as t ON i.object_id = t.object_id
        AND i.object_id = OBJECT_ID(N'Production.BillOfMaterials','U');
GO

E. Suppression d'une contrainte PRIMARY KEY en ligne

Les index créés suite à la création de contraintes PRIMARY KEY ou UNIQUE ne peuvent être supprimés qu'à l'aide de DROP INDEX. Ils sont supprimés à l'aide de l'instruction ALTER TABLE DROP CONSTRAINT. Pour plus d'informations, consultez ALTER TABLE.

L'exemple suivant supprime un index cluster avec une contrainte PRIMARY KEY en supprimant la contrainte. La table ProductCostHistory ne comporte aucune contrainte FOREIGN KEY. Si cela avait été le cas, ces contraintes auraient d'abord dû être supprimées.

USE AdventureWorks;
GO
-- Set ONLINE = OFF to execute this example on editions other than Enterprise Edition.
ALTER TABLE Production.ProductCostHistory
    DROP CONSTRAINT PK_ProductCostHistory_ProductID_StartDate
        WITH (ONLINE = ON);
GO

F. Suppression d'un index XML

L'exemple suivant supprime un index XML sur la table ProductModel.

USE AdventureWorks;
GO
DROP INDEX PXML_ProductModel_CatalogDescription 
    ON Production.ProductModel;
GO

G. Suppression d'un index cluster sur une table FILESTREAM

L'exemple suivant supprime un index cluster en ligne et déplace la table résultante (segment de mémoire) et les données FILESTREAM vers le schéma de partitionnement MyPartitionScheme en utilisant les clauses MOVE TO et FILESTREAM ON.

USE MyDatabase;
GO
DROP INDEX PK_MyClusteredIndex 
    ON dbo.MyTable 
    MOVE TO MyPartitionScheme
    FILESTREAM_ON MyPartitionScheme;
GO

Voir aussi

Référence

ALTER INDEX (Transact-SQL)

ALTER PARTITION SCHEME (Transact-SQL)

ALTER TABLE (Transact-SQL)

CREATE INDEX (Transact-SQL)

CREATE PARTITION SCHEME (Transact-SQL)

CREATE SPATIAL INDEX (Transact-SQL)

CREATE XML INDEX (Transact-SQL)

EVENTDATA (Transact-SQL)

sys.indexes (Transact-SQL)

sys.tables (Transact-SQL)

sys.filegroups (Transact-SQL)

sp_spaceused (Transact-SQL)

Concepts

Suppression d'index

Autres ressources

Détermination de l'espace disque requis par les index