DROP INDEX (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

Index XML

Index spatiaux

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

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

B. Suppression de plusieurs index

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

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

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

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

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

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

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

Voir aussi

Référence

Concepts

Autres ressources