DROP INDEX (Transact-SQL)

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Entfernt einen oder mehrere relationale Indizes, räumliche Indizes, gefilterte Indizes oder XML-Indizes aus der aktuellen Datenbank. Sie können einen gruppierten Index ablegen und die resultierende Tabelle in eine andere Dateigruppe oder ein Partitionsschema in einer einzelnen Transaktion verschieben, indem Sie die MOVE TO Option angeben.

Die DROP INDEX Anweisung gilt nicht für Indizes, die durch Definieren PRIMARY KEY oder UNIQUE Einschränkungen erstellt wurden. Verwenden Sie ALTER TABLE mit der Klausel, um die Einschränkung und den DROP CONSTRAINT entsprechenden Index zu entfernen.

Transact-SQL-Syntaxkonventionen

Syntax

Syntax für SQL Server (alle Optionen außer Dateigruppe und Dateistream gelten für Azure SQL-Datenbank).

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" } ]
}

Syntax für Azure SQL-Datenbank.

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 }

Syntax für 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 }
[ ; ]

Argumente

IF EXISTS

Gilt für: SQL Server 2016 (13.x) und höhere Versionen.

Löscht den Standardindex nur, wenn dieser bereits vorhanden ist.

index_name

Der Name des zu verworfenen Indexes.

database_name

Der Name der Datenbank.

schema_name

Der Name des Schemas, zu dem die Tabelle oder Sicht gehören.

table_or_view_name

Der Name der Tabelle oder Sicht, die dem Index zugeordnet ist. Räumliche Indizes werden nur für Tabellen unterstützt.

Verwenden Sie zum Anzeigen eines Berichts über die Indizes zu einem Objekt die sys.indexes-Katalogsicht.

Azure SQL-Datenbank unterstützt das dreiteilige Namensformat: database_name.[schema_name].object_name wenn es sich database_name um die aktuelle Datenbank handelt oder die database_name ist tempdb und die object_name beginnt mit #.

<drop_clustered_index_option>

Gilt für: SQL Server 2008 (10.0.x) und höhere Versionen, SQL-Datenbank.

Steuert die Optionen für den gruppierten Index. Diese Optionen können nicht mit anderen Indextypen verwendet werden.

MAXDOP = max_degree_of_parallelism

Gilt für: SQL Server 2008 (10.0.x) und höhere Versionen, SQL-Datenbank (nur Leistungsstufen P2 und P3).

Überschreibt die Konfigurationsoption Max. Grad an Parallelität während des Indexvorgangs. Weitere Informationen finden Sie unter Konfigurieren des maximalen Parallelitätsgrads (Serverkonfigurationsoption). Wird verwendet MAXDOP , um die Anzahl der Prozessoren zu begrenzen, die in einer parallelen Planausführung verwendet werden. Maximal sind 64 Prozessoren zulässig.

max_degree_of_parallelism kann einer der folgenden Werte sein:

Weitere Informationen finden Sie unter Konfigurieren von Parallelindexvorgängen.

ONLINE = ON | OFF

Gilt für: SQL Server 2008 (10.0.x) und höhere Versionen, Azure SQL-Datenbank.

Gibt an, ob die zugrunde liegenden Tabellen und zugeordneten Indizes für Abfragen und Datenänderungen während des Indexvorgangs verfügbar sind. Der Standardwert ist OFF.

• ON: Langfristige Tabellensperren werden nicht gehalten. Abfragen oder Updates der zugrunde liegenden Tabelle können fortgesetzt werden.

• OFF: Tabellensperren werden angewendet, und die Tabelle ist während des Indexvorgangs nicht verfügbar.

Die ONLINE Option kann nur angegeben werden, wenn Sie gruppierte Indizes ablegen. Weitere Informationen finden Sie im Abschnitt Hinweise.

MOVE TO { partition_scheme_name ( column_name ) | filegroup_name | "default" }

Gilt für: SQL Server 2008 (10.0.x) und höhere Versionen SQL-Datenbank wird als Dateigruppenname unterstützt"default".

Gibt einen Speicherort an, an den die Datenzeilen verschoben werden, die sich zurzeit auf der Blattebene des gruppierten Index befinden. Die Daten werden in einen Heap an den neuen Speicherort verschoben. Als neuen Speicherort können Sie entweder ein Partitionsschema oder eine Dateigruppe angeben, das Partitionsschema oder die Dateigruppe muss jedoch bereits vorhanden sein. MOVE TO ist nicht gültig für indizierte Ansichten oder nicht gruppierte Indizes. Wenn kein Partitionsschema oder eine Dateigruppe angegeben ist, befindet sich die resultierende Tabelle im gleichen Partitionsschema oder derselben Dateigruppe wie für den gruppierten Index definiert.

Wenn ein gruppierter Index mithilfe MOVE TOvon verworfen wird, werden alle nicht gruppierten Indizes in der Basistabelle neu erstellt, aber sie verbleiben in ihren ursprünglichen Dateigruppen oder Partitionsschemas. Wenn die Basistabelle in eine andere Dateigruppe oder ein anderes Partitionsschema verschoben wird, werden die nicht gruppierten Indizes nicht verschoben, um mit dem neuen Speicherort der Basistabelle (Heap) übereinzutreffen. Deshalb ist es möglich, dass die nicht gruppierten Indizes nicht mehr mit dem Heap ausgerichtet sind, selbst wenn sie vorher mit dem gruppierten Index ausgerichtet wurden. Weitere Informationen zur Partitionierten Indexausrichtung finden Sie unter Partitionierte Tabellen und Indizes.

partition_scheme_name ( column_name )

Gilt für: SQL Server 2008 (10.0.x) und höhere Versionen, SQL-Datenbank.

Gibt ein Partitionsschema als Speicherort für die resultierende Tabelle an. Das Partitionsschema muss bereits erstellt werden, indem entweder CREATE PARTITION SCHEME oder ALTER PARTITION SCHEME ausgeführt wird. Wird kein Speicherort angegeben und ist die Tabelle partitioniert, dann wird die Tabelle in das Partitionsschema des vorhandenen gruppierten Index aufgenommen.

Der Spaltenname im Schema ist nicht auf die Spalten in der Indexdefinition beschränkt. Jede beliebige Spalte in der Basistabelle kann angegeben werden.

filegroup_name

Gilt für: SQL Server 2008 (10.0.x) und höhere Versionen

Gibt eine Dateigruppe als Speicherort für die resultierende Tabelle an. Wenn kein Speicherort angegeben ist und die Tabelle nicht partitioniert wird, wird die resultierende Tabelle in derselben Dateigruppe wie der gruppierte Index enthalten. Die Dateigruppe muss bereits vorhanden sein.

"default"

Gibt den Standardspeicherort für die resultierende Tabelle an.

FILESTREAM_ON { partition_scheme_name | filestream_filegroup_name | "default" }

Gilt für: SQL Server 2008 (10.0.x) und höhere Versionen

Gibt einen Speicherort an, an den die FILESTREAM-Tabelle verschoben wird, die sich zurzeit auf der Blattebene des gruppierten Index befindet. Die Daten werden in einen Heap an den neuen Speicherort verschoben. Als neuen Speicherort können Sie entweder ein Partitionsschema oder eine Dateigruppe angeben, das Partitionsschema oder die Dateigruppe muss jedoch bereits vorhanden sein. FILESTREAM ON ist nicht gültig für indizierte Ansichten oder nicht gruppierte Indizes. Wenn kein Partitionsschema angegeben ist, befinden sich die Daten im gleichen Partitionsschema wie für den gruppierten Index definiert.

partition_scheme_name

Gibt ein Partitionsschema für FILESTREAM-Daten an. Das Partitionsschema muss bereits erstellt werden, indem entweder CREATE PARTITION SCHEME oder ALTER PARTITION SCHEME ausgeführt wird. Wird kein Speicherort angegeben und ist die Tabelle partitioniert, dann wird die Tabelle in das Partitionsschema des vorhandenen gruppierten Index aufgenommen.

Wenn Sie ein Partitionsschema für MOVE TOangeben, müssen Sie dasselbe Partitionsschema für FILESTREAM ON.

filestream_filegroup_name

Gibt eine FILESTREAM-Dateigruppe für FILESTREAM-Daten an. Wenn kein Speicherort angegeben ist und die Tabelle nicht partitioniert wird, werden die Daten in der standardmäßigen FILESTREAM-Dateigruppe enthalten.

"default"

Gibt den Standardspeicherort für FILESTREAM-Daten an.

Hinweise

Wenn ein nicht gruppierter Index gelöscht wird, wird die Indexdefinition aus Metadaten entfernt, und die Indexdatenseiten (die B-Struktur) werden aus den Datenbankdateien entfernt. Wenn ein gruppierter Index gelöscht wird, wird die Indexdefinition aus den Metadaten entfernt und die auf der Blattebene des gruppierten Indexes gespeicherten Datenzeilen werden in der daraus resultierenden, nicht sortierten Tabelle (Heap) gespeichert. Der gesamte Speicherplatz, der vorher für den Index benötigt wurde, wird wieder freigegeben. Dieser Speicherplatz kann dann für beliebige Datenbankobjekte verwendet werden.

Ein Index kann nicht gelöscht werden, wenn die Dateigruppe, in der sie sich befindet, offline ist oder schreibgeschützt festgelegt ist.

Wenn der gruppierte Index einer indizierten Ansicht verworfen wird, werden alle nicht gruppierten Indizes und automatisch erstellten Statistiken in derselben Ansicht automatisch gelöscht. Manuell erstellte Statistiken werden nicht gelöscht.

Die Syntax <table_or_view_name>.<index_name> wird aus Gründen der Abwärtskompatibilität beibehalten. Ein XML-Index oder ein räumlicher Index kann nicht mithilfe der abwärtskompatiblen Syntax gelöscht werden.

Wenn Indizes mit 128 oder mehr Extents gelöscht werden, verschiebt die Datenbank-Engine die tatsächlichen Seitendeallokationen und die damit verbundenen Sperren bis nach dem Commit der Transaktion.

Gelegentlich werden Indizes gelöscht und neu erstellt, um den Index neu zu organisieren oder neu zu erstellen, beispielsweise um einen neuen Füllfaktorwert anzuwenden oder um Daten nach dem Massenladen neu zu organisieren. Dazu ist die Verwendung von ALTER INDEX effizienter, insbesondere für gruppierte Indizes. ALTER INDEX REBUILD verfügt über Optimierungen, um den Aufwand der Neuerstellung der nicht gruppierten Indizes zu verhindern.

Verwenden von Optionen mit DROP INDEX

Sie können die folgenden Indexoptionen festlegen, wenn Sie einen gruppierten Index ablegen: MAXDOP, , ONLINEund MOVE TO.

Wird MOVE TO verwendet, um den gruppierten Index abzulegen und die resultierende Tabelle in eine andere Dateigruppe oder ein Partitionsschema in einer einzelnen Transaktion zu verschieben.

Wenn Sie Abfragen und Änderungen an den zugrunde liegenden Daten angeben ONLINE = ONund zugeordnete nicht gruppierte Indizes nicht durch die DROP INDEX Transaktion blockiert werden. Online kann jeweils nur ein gruppierter Index gelöscht werden. Eine vollständige Beschreibung der ONLINE Option finden Sie unter CREATE INDEX.

Sie können einen gruppierten Index nicht online ablegen, wenn der Index in einer Ansicht deaktiviert ist oder Text, ntext, Bild, varchar(max), nvarchar(max), varbinary(max) oder XML-Spalten in den Zeilen auf Blattebene enthält.

Die Verwendung der ONLINE = ON Optionen MOVE TO erfordert mehr temporären Speicherplatz.

Nachdem ein Index gelöscht wurde, wird der resultierende Heap in der sys.indexes Katalogansicht in NULL der name Spalte angezeigt. Um den Tabellennamen anzuzeigen, müssen Sie sys.indexes mit sys.tables der object_id Unter Beispiel D finden Sie eine Beispielabfrage.

Auf Multiprozessorcomputern, auf denen SQL Server 2005 Enterprise Edition oder höher ausgeführt wird, können weitere Prozessoren verwendet werden, DROP INDEX um die Überprüfungs- und Sortiervorgänge auszuführen, die mit dem Ablegen des gruppierten Indexes verbunden sind, genau wie bei anderen Abfragen. Sie können die Anzahl der Prozessoren, die zum Ausführen der DROP INDEX Anweisung verwendet werden, manuell konfigurieren, indem Sie die MAXDOP Indexoption angeben. Weitere Informationen finden Sie unter Konfigurieren von Parallelindexvorgängen.

Beim Löschen eines gruppierten Indexes behalten die entsprechenden Heappartitionen ihre Einstellung für die Datenkomprimierung bei, sofern nicht das Partitionierungsschema geändert wird. Wenn das Partitionierungsschema geändert wird, werden alle Partitionen in einen nicht komprimierten Zustand (DATA_COMPRESSION = NONE) neu erstellt. Um einen gruppierten Index zu löschen und das Partitionierungsschema zu ändern, sind die beiden folgenden Schritte erforderlich:

1. Löschen Sie den gruppierten Index.

2. Ändern Sie die Tabelle mithilfe einer ALTER TABLE ... REBUILD ... Option, die die Komprimierungsoption angibt.

Wenn ein gruppierter Index abgelegt OFFLINEwird, werden nur die oberen Ebenen gruppierter Indizes entfernt. Daher ist der Vorgang schnell. Wenn ein gruppierter Index abgelegt ONLINEwird, erstellt SQL Server den Heap zweimal neu, einmal für Schritt 1 und einmal für Schritt 2. Weitere Informationen zur Datenkomprimierung finden Sie unter "Datenkomprimierung".

XML-Indizes

Optionen können nicht angegeben werden, wenn Sie einenXML-Index ablegen. Außerdem können Sie die <table_or_view_name>.<index_name> Syntax nicht verwenden. Wird ein primärer XML-Index gelöscht, werden alle dazugehörigen sekundären XML-Indizes automatisch gelöscht. Weitere Informationen finden Sie unter XML-Indizes (SQL Server).

Räumlichkeitsindizes

Räumliche Indizes werden nur für Tabellen unterstützt. Wenn Sie einen räumlichen Index ablegen, können Sie keine Optionen angeben oder verwenden .<index_name>. Die korrekte Syntax lautet wie folgt:

DROP INDEX <spatial_index_name> ON <spatial_table_name>;

Weitere Informationen zu räumlichen Indizes finden Sie unter Übersicht über räumliche Indizes.

Berechtigungen

Zum Ausführen von DROP INDEX benötigen Sie mindestens die ALTER-Berechtigung für die Tabelle oder Ansicht. Über diese Berechtigungen verfügen standardmäßig die Mitglieder der festen Serverrolle sysadmin und die Mitglieder der festen Datenbankrollen db_ddladmin und db_owner .

Beispiele

Die Transact-SQL-Codebeispiele in diesem Artikel verwenden die AdventureWorks2022- oder AdventureWorksDW2022-Beispieldatenbank, die Sie von der Homepage Microsoft SQL Server Samples and Community Projects herunterladen können.

A. Ablegen eines Indexes

Im folgenden Beispiel wird der IX_ProductVendor_BusinessEntityID-Index für die ProductVendor-Tabelle in der AdventureWorks2022-Datenbank gelöscht.

DROP INDEX IX_ProductVendor_BusinessEntityID
    ON Purchasing.ProductVendor;
GO

B. Mehrere Indizes ablegen

Im folgenden Beispiel werden zwei Indizes in einer einzelnen Transaktion in der AdventureWorks2022-Datenbank gelöscht.

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

C. Ablegen eines gruppierten Indexes online und Festlegen der MAXDOP-Option

Im folgenden Beispiel wird ein gruppierter Index gelöscht, wobei für die Option ONLINE die Einstellung ON und für MAXDOP die Einstellung 8 festgelegt ist. Da die MOVE TO Option nicht angegeben wurde, wird die resultierende Tabelle in derselben Dateigruppe wie der Index gespeichert.

Gilt für: SQL Server 2008 (10.0.x) und höhere Versionen, SQL-Datenbank.

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

D: Ablegen eines gruppierten Indexes online und Verschieben der Tabelle in eine neue Dateigruppe

Im folgenden Beispiel wird ein gruppierter Index online gelöscht, und die daraus resultierende Tabelle (Heap) wird in die Dateigruppe NewGroup verschoben, wofür die MOVE TO -Klausel verwendet wird. Die Katalogsichten sys.indexes, sys.tablesund sys.filegroups werden abgefragt, um die Platzierung von Index und Tabelle in den Dateigruppen vor und nach der Verschiebung zu prüfen. Ab SQL Server 2016 (13.x) können Sie die DROP INDEX IF EXISTS Syntax verwenden.

Gilt für: SQL Server 2008 (10.0.x) und höhere Versionen

--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. Löschen einer PRIMARY KEY-Einschränkung online

Indizes, die als Ergebnis der Erstellung PRIMARY KEY oder UNIQUE Einschränkung erstellt werden, können nicht mit der Verwendung DROP INDEXverworfen werden. Sie werden mit der ALTER TABLE DROP CONSTRAINT Anweisung verworfen. Weitere Informationen finden Sie unter ALTER TABLE.

Im folgenden Beispiel wird ein gruppierter Index mit einer PRIMARY KEY Einschränkung gelöscht, indem die Einschränkung gelöscht wird. Die ProductCostHistory Tabelle hat keine FOREIGN KEY Einschränkungen. Wenn dem so wäre, müssten diese Einschränkungen zuerst entfernt werden.

-- 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. Ablegen eines XML-Indexes

Im folgenden Beispiel wird der XML-Index für die ProductModel-Tabelle in der AdventureWorks2022-Datenbank gelöscht.

DROP INDEX PXML_ProductModel_CatalogDescription
    ON Production.ProductModel;

G. Ablegen eines gruppierten Indexes in einer FILESTREAM-Tabelle

Im folgenden Beispiel wird ein gruppierter Index online gelöscht, und die daraus resultierende Tabelle (Heap) und die FILESTREAM-Daten werden in das MyPartitionScheme-Partitionsschema verschoben, wofür die MOVE TO-Klausel und die FILESTREAM ON-Klausel verwendet werden.

Gilt für: SQL Server 2008 (10.0.x) und höhere Versionen

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

Zugehöriger Inhalt

• 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