DROP INDEX (Transact-SQL)

Syntax

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

Argumente

• index_name
  Der Name des zu löschenden Index.

• database_name
  Der Name der Datenbank.

• schema_name
  Der Name des Schemas, zu dem die Tabelle oder Sicht gehört.

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

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

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

• MAXDOP = max_degree_of_parallelism
  Setzt die Konfigurationsoption max degree of parallelism für die Dauer des Indexerstellungsvorgangs außer Kraft. Weitere Informationen finden Sie unter max degree of parallelism (Option). Verwenden Sie MAXDOP, um die Anzahl von Prozessoren zu begrenzen, die bei der Ausführung paralleler Pläne verwendet werden. Die maximale zulässige Anzahl sind 64 Prozessoren.

  Wichtig
  MAXDOP ist für räumliche Indizes oder XML-Indizes nicht zulässig.

  Mögliche Werte für max_degree_of_parallelism sind:

  • 1
    Unterdrückt die Generierung paralleler Pläne.

  • >1
    Schränkt die maximale Anzahl von Prozessoren, die bei einem parallelen Indexvorgang verwendet werden, auf die angegebene Anzahl ein.

  • 0 (Standard)
    Verwendet die tatsächliche Anzahl an Prozessoren oder weniger Prozessoren, was von der aktuellen Systemarbeitsauslastung abhängig ist.

  Weitere Informationen finden Sie unter Konfigurieren von Parallelindexvorgänge.

  Hinweis
  Parallele Indexvorgänge sind nur in der SQL Server Enterprise, Developer und Evaluation Edition verfügbar.

• ONLINE = ON | OFF
  Gibt an, ob zugrunde liegende Tabellen und dazugehörige Indizes während des Indexerstellungsvorgangs für Abfragen und Datenänderungen zur Verfügung stehen. Der Standardwert ist OFF.

  • ON
    Langfristigen Tabellensperren werden nicht aufrechterhalten. Abfragen oder Aktualisierungen der zugrunde liegenden Tabelle können fortgesetzt werden.

  • OFF
    Tabellensperren werden angewendet, und die Tabelle steht für die Dauer der Indexerstellungsvorgangs nicht zur Verfügung.

  Die Option ONLINE kann nur dann angegeben werden, wenn Sie gruppierte Indizes löschen. Weitere Informationen finden Sie im Abschnitt mit Hinweisen.

  Hinweis
  Online-Indexvorgänge sind nur in der SQL Server Enterprise, Developer und Evaluation Edition verfügbar.

• MOVE TO { partition_scheme_name**(column_name)** | filegroup_name | "default"
  Gibt einen Speicherort an, an den die Datenzeilen verschoben werden, die sich zurzeit auf der Blattebene des gruppierten Indexes 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 für indizierte Sichten oder nicht gruppierte Indizes nicht gültig. Wird kein Partitionsschema oder keine Dateigruppe angegeben, dann befindet sich die daraus resultierende Tabelle entsprechend der Definition für den gruppierten Index im Partitionsschema oder in der Dateigruppe.

  Wird ein gruppierter Index mit MOVE TO gelöscht, werden alle nicht gruppierten Indizes für die Basistabelle neu erstellt. Sie verbleiben jedoch 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 dem neuen Speicherort der Basistabelle (Heap) zu entsprechen. 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 Ausrichtung des partitionierten Indexes finden Sie unter Spezielle Richtlinien für partitionierte Indizes.

  • partition_scheme_name**(column_name)**
    Gibt ein Partitionsschema als Speicherort für die resultierende Tabelle an. Das Partitionsschema muss bereits durch Ausführen von CREATE PARTITION SCHEME oder ALTER PARTITION SCHEME erstellt worden sein. Wird kein Speicherort angegeben und ist die Tabelle partitioniert, wird die Tabelle in das Partitionsschema des vorhandenen gruppierten Indexes aufgenommen.

    Für den Spaltennamen im Schema gibt es keine Beschränkung auf die Spalten in der Indexdefinition. Jede beliebige Spalte in der Basistabelle kann angegeben werden.

  • filegroup_name
    Gibt eine Dateigruppe als Speicherort für die resultierende Tabelle an. Wird kein Speicherort angegeben und ist die Tabelle nicht partitioniert, wird die resultierende Tabelle in die Dateigruppe aufgenommen, in der sich der gruppierte Index befindet. Die Dateigruppe muss bereits vorhanden sein.

  • "default"
    Gibt den Standardspeicherort für die resultierende Tabelle an.

    Hinweis

    In diesem Kontext ist DEFAULT kein Schlüsselwort. Es ist ein Bezeichner für die Standarddateigruppe und muss eingeschränkt werden, wie in MOVE TO "default" oder MOVE TO [default]. Wenn "default" angegeben ist, muss die QUOTED_IDENTIFIER-Option in der aktuellen Sitzung auf ON festgelegt sein. Dies ist die Standardeinstellung. Weitere Informationen finden Sie unter SET QUOTED_IDENTIFIER (Transact-SQL).

• FILESTREAM_ON { partition_scheme_name | filestream_filegroup_name | "default" }
  Gibt einen Speicherort an, an den die FILESTREAM-Tabelle verschoben wird, die sich zurzeit auf der Blattebene des gruppierten Indexes 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 für indizierte Sichten oder nicht gruppierte Indizes unzulässig. Wird kein Partitionsschema angegeben, werden die Daten in demselben Partitionsschema platziert, das für den gruppierten Index definiert war.

  • partition_scheme_name
    Gibt ein Partitionsschema für FILESTREAM-Daten an. Das Partitionsschema muss bereits durch Ausführen von CREATE PARTITION SCHEME oder ALTER PARTITION SCHEME erstellt worden sein. Wird kein Speicherort angegeben und ist die Tabelle partitioniert, wird die Tabelle in das Partitionsschema des vorhandenen gruppierten Indexes aufgenommen.

    Wenn Sie ein Partitionsschema für MOVE TO angeben, müssen Sie dasselbe Partitionsschema für FILESTREAM ON verwenden.

  • filestream_filegroup_name
    Gibt eine FILESTREAM-Dateigruppe für FILESTREAM-Daten an. Wenn kein Speicherort angegeben und die Tabelle nicht partitioniert ist, werden die Daten in die FILESTREAM-Standarddateigruppe eingefügt.

  • "default"
    Gibt den Standardspeicherort für FILESTREAM-Daten an.

    Hinweis

    In diesem Kontext ist DEFAULT kein Schlüsselwort. Es ist ein Bezeichner für die Standarddateigruppe und muss eingeschränkt werden, wie in MOVE TO "default" oder MOVE TO [default]. Wenn "default" angegeben wird, dann muss für die Option QUOTED_IDENTIFIER für die aktuelle Sitzung ON festgelegt werden. Dies ist die Standardeinstellung. Weitere Informationen finden Sie unter SET QUOTED_IDENTIFIER (Transact-SQL).

Hinweise

Wenn ein nicht gruppierter Index gelöscht wird, wird die Indexdefinition aus den Metadaten entfernt, und die Indexdatenseiten (in der 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 anschließend für beliebige Datenbankobjekte verwendet werden.

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

Wenn der gruppierte Index einer indizierten Sicht gelöscht wird, dann werden alle nicht gruppierten Indizes und automatisch erstellten Statistiken dieser Sicht automatisch gelöscht. Manuell erstellte Statistiken werden nicht gelöscht.

Die Syntaxtable_or_view_name**.**index_name wurde aus Gründen der Abwärtskompatibilität beibehalten. XML-Indizes oder räumliche Indizes können mit abwärtskompatibler Syntax nicht gelöscht werden.

Werden Indizes mit 128 oder mehr Blöcken gelöscht, verzögert das Database Engine (Datenbankmodul) die Aufhebung der Seitenzuordnungen und der dazugehörigen Sperren, bis der Commit für die Transaktion ausgeführt ist. Weitere Informationen finden Sie unter Löschen und Neuerstellen großer Objekte.

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. Für diesen Vorgang ist die Verwendung von ALTER INDEX effizienter, insbesondere bei gruppierten Indizes. ALTER INDEX REBUILD verwendet Optimierungen, um den Aufwand der Neuerstellung der nicht gruppierten Indizes zu vermeiden.

Verwenden von Optionen mit DROP INDEX

XML-Indizes

Räumliche Indizes

Berechtigungen

Für das Ausführen von DROP INDEX ist mindestens eine ALTER-Berechtigung für die Tabelle oder Sicht erforderlich. Ü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

A. Löschen eines Index

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

B. Löschen mehrerer Indizes

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

C. Online-Löschen eines gruppierten Indexes und Festlegen der Option MAXDOP

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 Option MOVE TO nicht angegeben wurde, wird die daraus resultierende Tabelle in der gleichen Dateigruppe wie der Index gespeichert.

Hinweis
Dieses Beispiel kann nur in SQL Server 2005 Enterprise Edition oder höher ausgeführt werden.

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

D. Online-Löschen eines gruppierten Indexes und Verschieben der Tabelle in eine neue Dateigruppe

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

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. Löschen eines XML-Index

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

G. Löschen eines gruppierten Indexes aus einer FILESTREAM-Tabelle

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

Siehe auch

Verweis

Konzepte

Andere Ressourcen