DROP INDEX (Transact-SQL)

S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Supprime un ou plusieurs index relationnels, spatiaux ou XML de la base de données active. Vous pouvez supprimer un index cluster et déplacer la table obtenue vers un autre groupe de fichiers ou schéma de partition dans une seule transaction en spécifiant l’option MOVE TO .

L’instruction DROP INDEX ne s’applique pas aux index créés en définissant ou UNIQUE en contraintesPRIMARY KEY. Pour supprimer la contrainte et l’index correspondant, utilisez ALTER TABLE avec la DROP CONSTRAINT clause.

Conventions de la syntaxe Transact-SQL

Syntaxe

Syntaxe pour SQL Server (toutes les options à l’exception du groupe de fichiers et du flux de fichiers s’appliquent à Azure SQL Database).

DROP INDEX [ IF EXISTS ]
{ <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.table_or_view_name | schema_name.table_or_view_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" } ]
}

Syntaxe pour Azure SQL Database.

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

<drop_relational_or_xml_or_spatial_index> ::=
    index_name ON <object>

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

Syntaxe pour Azure Synapse Analytics and Analytics Platform System (PDW).

DROP INDEX index_name ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
[ ; ]

Arguments

IF EXISTS

S’applique à : SQL Server 2016 (13.x) et versions ultérieures.

Supprime, de manière conditionnelle, l’index uniquement s’il existe déjà.

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 vue ou la table.

table_or_view_name

Nom de la table ou de la vue associée à l’index. Les index spatiaux sont pris en charge uniquement dans les tables.

Pour afficher un rapport des index relatifs à un objet, utilisez la vue de catalogue sys.indexes.

Azure SQL Database prend en charge le format de nom en trois parties : database_name.[schema_name].object_name quand il database_name s’agit de la base de données active, ou que le database_name est tempdb et que le object_name commence par #.

<drop_clustered_index_option>

S’applique à : SQL Server 2008 (10.0.x) et versions ultérieures, SQL Database.

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

S’applique à : SQL Server 2008 (10.0.x) et versions ultérieures, SQL Database (niveaux de performances P2 et P3 uniquement).

Remplace l’option de configuration max degree of parallelism pendant l’opération d’index. Pour plus d’informations, consultez Configurer le degré maximal de parallélisme (option de configuration de serveur). Permet MAXDOP de limiter le nombre de processeurs utilisés dans une exécution de plan parallèle. Le nombre maximal de processeurs est égal à 64.

max_degree_of_parallelism peut être l’une des valeurs suivantes.

Pour plus d’informations, consultez Configurer des opérations d’index parallèles.

ONLINE = ON | OFF

S’applique à : SQL Server 2008 (10.0.x) et versions ultérieures, Azure SQL Database.

Indique si les tables sous-jacentes et les index associés sont disponibles pour les requêtes et la modification de données pendant l'opération d'index. Par défaut, il s’agit de OFF.

• ON: les verrous de table à long terme ne sont pas conservés. 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 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.

MOVE TO { partition_scheme_name ( column_name ) | filegroup_name | « default » }

S’applique à : SQL Server 2008 (10.0.x) et ultérieur. SQL Database prend en charge "default" le nom du groupe de fichiers.

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 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 se trouve dans 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 MOVE TOde , tous les index non cluster de la table de base sont reconstruits, mais ils restent dans leurs groupes de fichiers ou schémas de partition d’origine. Si la table de base est déplacée vers un autre groupe de fichiers ou schéma de partition, les index non cluster ne sont pas déplacés pour coïncider avec le nouvel emplacement de la table de base (tas). 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 tables et index partitionnés.

partition_scheme_name (column_name)

S’applique à : SQL Server 2008 (10.0.x) et versions ultérieures, SQL Database.

Spécifie un schéma de partition comme emplacement de la table résultante. Le schéma de partition doit déjà être créé, en exécutant CREATE PARTITION SCHEME ou 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 colonne dans le schéma n’est pas limité aux colonnes de la définition d’index. Toute colonne dans la table de base peut être spécifiée.

filegroup_name

S’applique à : SQL Server 2008 (10.0.x) et ultérieur.

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.

FILESTREAM_ON { partition_scheme_name filestream_filegroup_name | | « default » }

S’applique à : SQL Server 2008 (10.0.x) et ultérieur.

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 se trouvent dans le même schéma de partition que celui 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à être créé, en exécutant CREATE PARTITION SCHEME ou 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 spécifié 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.

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 (l’arborescence B) 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 sur lecture seule.

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

La syntaxe <table_or_view_name>.<index_name> est conservée pour la compatibilité descendante. Un index XML ou un index spatial ne peut pas être supprimé à l’aide de la syntaxe compatible descendante.

Lorsque des index avec 128 extensions ou plus sont supprimés, le Moteur de base de données diffère les désallocations de page réelles et leurs verrous associés jusqu'à la validation de la transaction.

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 en cluster. ALTER INDEX REBUILD a des optimisations pour éviter la surcharge de reconstruction des index non cluster.

Utiliser des options avec DROP INDEX

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

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

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

Vous ne pouvez pas supprimer un index cluster en ligne si l’index est désactivé sur une vue ou contient du texte, ntext, image, varchar(max), nvarchar(max), varbinary(max) ou des colonnes xml dans les lignes de données de niveau feuille.

L’utilisation des options nécessite MOVE TO davantage d’espace ONLINE = ON disque temporaire.

Une fois qu’un index est supprimé, le tas résultant apparaît dans l’affichage sys.indexes catalogue avec NULL dans la name colonne. Pour afficher le nom de la table, joignez-vous sys.indexes sur sys.tables object_id. Pour un exemple de requête, reportez-vous à l'exemple D.

Sur les ordinateurs multiprocesseurs exécutant SQL Server 2005 Enterprise edition ou version ultérieure, DROP INDEX peuvent utiliser davantage de processeurs pour effectuer les opérations d’analyse et de tri associées à la suppression de l’index cluster, comme d’autres requêtes. Vous pouvez configurer manuellement le nombre de processeurs utilisés pour exécuter l’instruction DROP INDEX en spécifiant l’option MAXDOP d’index. Pour plus d’informations, consultez Configurer des 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 à 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. Modifiez la table à l’aide d’une ALTER TABLE ... REBUILD ... option spécifiant l’option de compression.

Lorsqu’un index cluster est supprimé OFFLINE, seuls les niveaux supérieurs des index cluster sont supprimés ; par conséquent, l’opération est rapide. Lorsqu’un index cluster est supprimé ONLINE, SQL Server reconstruit le tas deux fois, une fois pour l’étape 1 et une fois pour l’étape 2. Pour plus d’informations sur la compression des données, consultez Compression des données.

Index XML

Les options ne peuvent pas être spécifiées lorsque vous supprimez un indexXML. En outre, vous ne pouvez pas utiliser la <table_or_view_name>.<index_name> syntaxe. Lorsqu'un index XML primaire est supprimé, tous les index XML secondaires associés sont également supprimés. Pour plus d’informations, consultez Index XML (SQL Server).

Index spatiaux

Les index spatiaux sont pris en charge uniquement dans les tables. Lorsque vous supprimez un index spatial, vous ne pouvez pas spécifier d’options ni d’utilisation .<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 Vue d’ensemble des 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

Les exemples de code Transact-SQL de cet article sont fondés sur l’échantillon de base de données AdventureWorks2022 ou AdventureWorksDW2022 fourni, que vous pouvez télécharger à partir de la page d’accueil Échantillons et projets communautaires Microsoft SQL Server.

A. Supprimer un index

L’exemple suivant supprime l’index IX_ProductVendor_BusinessEntityID sur la table ProductVendor de la base de données AdventureWorks2022.

DROP INDEX IX_ProductVendor_BusinessEntityID
    ON Purchasing.ProductVendor;
GO

B. Supprimer plusieurs index

L’exemple suivant supprime les deux index dans une seule transaction de la base de données AdventureWorks2022.

DROP INDEX
    IX_PurchaseOrderHeader_EmployeeID ON Purchasing.PurchaseOrderHeader,
    IX_Address_StateProvinceID ON Person.Address;
GO

C. Supprimer un index cluster en ligne et définir l’option MAXDOP

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

S’applique à : SQL Server 2008 (10.0.x) et versions ultérieures, SQL Database.

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

D. Supprimer un index cluster en ligne et déplacer 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.tableset 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. À compter de SQL Server 2016 (13.x), vous pouvez utiliser la DROP INDEX IF EXISTS syntaxe.

S’applique à : SQL Server 2008 (10.0.x) et ultérieur.

--Create a clustered index on the PRIMARY filegroup if the index does not exist.
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.
IF NOT EXISTS (SELECT name FROM sys.filegroups
                WHERE name = N'NewGroup')
    BEGIN
    ALTER DATABASE AdventureWorks2022
        ADD FILEGROUP NewGroup;
    ALTER DATABASE AdventureWorks2022
        ADD FILE (NAME = File1,
            FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\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. Supprimer une contrainte PRIMARY KEY en ligne

Les index créés suite à la création PRIMARY KEY ou UNIQUE aux contraintes ne peuvent pas être supprimés à l’aide DROP INDEXde . 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 PRIMARY KEY contrainte en supprimant la contrainte. La ProductCostHistory table n’a aucune FOREIGN KEY contrainte. Si cela avait été le cas, ces contraintes auraient d'abord dû être supprimées.

-- Set ONLINE = OFF to execute this example on editions other than Enterprise Edition.
ALTER TABLE Production.TransactionHistoryArchive
DROP CONSTRAINT PK_TransactionHistoryArchive_TransactionID
WITH (ONLINE = ON);

F. Supprimer un index XML

L’exemple suivant dépose un index XML sur la table ProductModel de la base de données AdventureWorks2022.

DROP INDEX PXML_ProductModel_CatalogDescription
    ON Production.ProductModel;

G. Supprimer 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.

S’applique à : SQL Server 2008 (10.0.x) et ultérieur.

DROP INDEX PK_MyClusteredIndex
    ON dbo.MyTable
    WITH (MOVE TO MyPartitionScheme,
          FILESTREAM_ON MyPartitionScheme);
GO

Contenu connexe

• 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
• sys.tables
• sys.filegroups
• sp_spaceused