ALTER INDEX (Transact-SQL)

Gilt für:SQL ServerAzure SQL-DatenbankAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

Ändert einen vorhandenen Tabellen- oder Sichtindex (Rowstore, Columnstore oder XML), indem der Index deaktiviert, neu erstellt oder neu organisiert wird oder indem Optionen für den Index festgelegt werden.

Transact-SQL-Syntaxkonventionen

Syntax

-- Syntax for SQL Server and Azure SQL Database

ALTER INDEX { index_name | ALL } ON <object>
{
      REBUILD {
            [ PARTITION = ALL [ WITH ( <rebuild_index_option> [ ,...n ] ) ] ]
          | [ PARTITION = partition_number [ WITH ( <single_partition_rebuild_index_option> [ ,...n ] ) ] ]
      }
    | DISABLE
    | REORGANIZE  [ PARTITION = partition_number ] [ WITH ( <reorganize_option>  ) ]
    | SET ( <set_index_option> [ ,...n ] )
    | RESUME [WITH (<resumable_index_option> [, ...n])]
    | PAUSE
    | ABORT
}
[ ; ]

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

<rebuild_index_option> ::=
{
      PAD_INDEX = { ON | OFF }
    | FILLFACTOR = fillfactor
    | SORT_IN_TEMPDB = { ON | OFF }
    | IGNORE_DUP_KEY = { ON | OFF }
    | STATISTICS_NORECOMPUTE = { ON | OFF }
    | STATISTICS_INCREMENTAL = { ON | OFF }
    | ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF }
    | RESUMABLE = { ON | OFF }
    | MAX_DURATION = <time> [MINUTES]
    | ALLOW_ROW_LOCKS = { ON | OFF }
    | ALLOW_PAGE_LOCKS = { ON | OFF }
    | MAXDOP = max_degree_of_parallelism
    | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE }
        [ ON PARTITIONS ( {<partition_number> [ TO <partition_number>] } [ , ...n ] ) ]
    | XML_COMPRESSION = { ON | OFF }
        [ ON PARTITIONS ( {<partition_number> [ TO <partition_number>] } [ , ...n ] ) ]}

<single_partition_rebuild_index_option> ::=
{
      SORT_IN_TEMPDB = { ON | OFF }
    | MAXDOP = max_degree_of_parallelism
    | RESUMABLE = { ON | OFF }
    | MAX_DURATION = <time> [MINUTES]
    | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE }
    | XML_COMPRESSION = { ON | OFF }
    | ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF }
}

<reorganize_option> ::=
{
       LOB_COMPACTION = { ON | OFF }
    |  COMPRESS_ALL_ROW_GROUPS =  { ON | OFF}
}

<set_index_option> ::=
{
      ALLOW_ROW_LOCKS = { ON | OFF }
    | ALLOW_PAGE_LOCKS = { ON | OFF }
    | OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }
    | IGNORE_DUP_KEY = { ON | OFF }
    | STATISTICS_NORECOMPUTE = { ON | OFF }
    | COMPRESSION_DELAY = { 0 | delay [Minutes] }
}

<resumable_index_option> ::=
 {
    MAXDOP = max_degree_of_parallelism
    | MAX_DURATION =<time> [MINUTES]
    | <low_priority_lock_wait>
 }

<low_priority_lock_wait> ::=
{
    WAIT_AT_LOW_PRIORITY ( MAX_DURATION = <time> [ MINUTES ] ,
                          ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } )
}

-- Syntax for Azure Synapse Analytics and Parallel Data Warehouse

ALTER INDEX { index_name | ALL }
    ON   [ schema_name. ] table_name
{
      REBUILD {
            [ PARTITION = ALL [ WITH ( <rebuild_index_option> ) ] ]
          | [ PARTITION = partition_number [ WITH ( <single_partition_rebuild_index_option> )] ]
      }
    | DISABLE
    | REORGANIZE [ PARTITION = partition_number ]
}
[;]

<rebuild_index_option> ::=
{
    DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }
        [ ON PARTITIONS ( {<partition_number> [ TO <partition_number>] } [ , ...n ] ) ]
    | XML_COMPRESSION = { ON | OFF }
        [ ON PARTITIONS ( {<partition_number> [ TO <partition_number>] } [ , ...n ] ) ]
}

<single_partition_rebuild_index_option> ::=
{
    DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }
    | XML_COMPRESSION = { ON | OFF }
}

Argumente

index_name

Der Name des Index. Indexnamen müssen für eine Tabelle oder Sicht eindeutig sein, können aber innerhalb einer Datenbank mehrfach vorkommen. Indexnamen müssen den Regeln für Bezeichner entsprechen.

ALL

Gibt alle Indizes an, die unabhängig vom Indextyp der Tabelle oder Sicht zugeordnet sind. Die Angabe von ALL verursacht bei der Anweisung einen Fehler, wenn mindestens ein Index in einer Offlinedateigruppe oder schreibgeschützten Dateigruppe enthalten ist oder der angegebene Vorgang für mindestens einen Indextyp nicht zulässig ist. In der folgenden Tabelle werden die Indexvorgänge und die nicht zulässigen Indextypen aufgelistet.

Verwendet bei diesem Vorgang das Schlüsselwort ALL	Erzeugt einen Fehler, wenn mindestens einer dieser Indextypen in der Tabelle enthalten ist
REBUILD WITH ONLINE = ON	XML-Index Räumlicher Index Columnstore-Index: Anwendungsbereich: SQL Server (ab SQL Server 2012 (11.x)) und Azure SQL-Datenbank
REBUILD PARTITION = partition_number	Nicht partitionierter Index, XML-Index, räumlicher Index oder deaktivierter Index
REORGANIZE	Indizes, für die ALLOW_PAGE_LOCKS auf OFF festgelegt wurde
REORGANIZE PARTITION = partition_number	Nicht partitionierter Index, XML-Index, räumlicher Index oder deaktivierter Index
IGNORE_DUP_KEY = ON	XML-Index Räumlicher Index Columnstore-Index: Anwendungsbereich: SQL Server (ab SQL Server 2012 (11.x)) und Azure SQL-Datenbank
ONLINE = ON	XML-Index Räumlicher Index Columnstore-Index: Anwendungsbereich: SQL Server (ab SQL Server 2012 (11.x)) und Azure SQL-Datenbank
RESUMABLE = ON	Fortsetzbare Indizes werden nicht unterstützt für Schlüsselwort ALL. Gilt für: SQL Server (ab SQL Server 2017 (14.x)) und Azure SQL-Datenbank

Wenn ALL mit PARTITION = partition_number angegeben ist, müssen alle Indizes ausgerichtet sein. Das bedeutet, dass sie auf der Grundlage der entsprechenden Partitionsfunktionen partitioniert sind. Das Verwenden von ALL mit PARTITION hat zur Folge, dass alle Indexpartitionen mit demselben Wert für partition_number neu erstellt oder neu organisiert werden. Weitere Informationen zu partitionierten Indizes finden Sie unter Partitioned Tables and 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. Verwenden Sie zum Anzeigen eines Berichts über die Indizes zu einem Objekt die sys.indexes-Katalogsicht.

SQL-Datenbank unterstützt das dreiteilige Namensformat „database_name.[schema_name].table_or_view_name“, wenn „database_name“ die aktuelle Datenbank bzw. „tempdb“ ist und „table_or_view_name“ mit # beginnt.

REBUILD [ WITH (<rebuild_index_option> [ ,... n]) ]

Gilt für: SQL Server (ab SQL Server 2012 (11.x)) und Azure SQL-Datenbank

Gibt an, dass der Index mit denselben Spalten, demselben Indextyp, demselben Eindeutigkeitsattribut und derselben Sortierreihenfolge neu erstellt wird. Diese Klausel entspricht DBCC DBREINDEX. Mit REBUILD wird ein deaktivierter Index aktiviert. Durch das Neuerstellen eines gruppierten Index werden nur dann die zugeordneten nicht gruppierten Indizes neu erstellt, wenn das Schlüsselwort ALL angegeben ist. Wenn keine Indexoptionen angegeben sind, werden die vorhandenen Werte der Indexoptionen angewandt, die in sys.indexes gespeichert sind. Für alle Indexoptionen, deren Werte nicht in sys.indexes gespeichert sind, wird der Standardwert angewandt, der in der Argumentdefinition der Option angegeben ist.

Wenn ALL angegeben ist und die zugrunde liegende Tabelle ein Heap ist, hat der REBUILD-Vorgang keine Auswirkungen auf die Tabelle. Alle nicht gruppierten Indizes, die der Tabelle zugeordnet sind, werden neu erstellt.

Der REBUILD-Vorgang kann minimal protokolliert werden, wenn für die Datenbank das massenprotokollierte oder einfache Wiederherstellungsmodell festgelegt ist.

Für Columnstore-Indizes wird durch den REBUILD-Vorgang Folgendes ausgelöst:

• Die Sortierreihenfolge wird nicht verwendet.
• Abrufen einer exklusiven Sperre für die Tabelle oder Partition, während der REBUILD-Vorgang ausgeführt wird. Die Daten sind während des REBUILD-Vorgangs „offline“ und nicht verfügbar. Dies gilt selbst bei Verwendung von NOLOCK, Read Committed-Momentaufnahmeisolation (Read Committed Snapshot Isolation, RCSI) oder Momentaufnahmeisolation.
• Komprimiert alle Daten im Columnstore neu. Während der REBUILD-Vorgang ausgeführt wird, sind zwei Kopien des Columnstore-Indexes vorhanden. Nach Abschluss des REBUILD-Vorgangs wird der ursprüngliche Columnstore-Index von SQL Server gelöscht.

Weitere Informationen finden Sie unter Neuorganisieren und Neuerstellen von Indizes.

PARTITION

Gibt an, dass nur eine Partition eines Indexes neu erstellt oder neu organisiert wird. PARTITION kann nicht angegeben werden, wenn index_name kein partitionierter Index ist.

PARTITION = ALL erstellt alle Partitionen neu.

partition_number

Die Partitionsnummer eines partitionierten Index, der neu erstellt oder neu organisiert werden soll. partition_number ist ein konstanter Ausdruck, der auf Variablen verweisen kann. Hierbei kann es sich um Funktionen oder Variablen mit benutzerdefiniertem Typ sowie um benutzerdefinierte Funktionen handeln, die jedoch nicht auf eine Transact-SQL-Anweisung verweisen können. partition_number muss vorhanden sein. Andernfalls schlägt die Anweisung fehl.

WITH (<single_partition_rebuild_index_option>)

Beim REBUILD-Vorgang für eine einzelne Partition (PARTITION = partition_number) können die Optionen SORT_IN_TEMPDB, MAXDOP, DATA_COMPRESSION und XML_COMPRESSION angegeben werden. XML-Indizes können bei einem REBUILD-Vorgang für eine einzelne Partition nicht angegeben werden.

DISABLE

Markiert den Index als deaktiviert und als nicht verfügbar für das Datenbank-Engine. Jeder Index kann deaktiviert werden. Die Indexdefinition eines deaktivierten Indexes bleibt weiterhin im Systemkatalog ohne zugrunde liegende Indexdaten bestehen. Durch das Deaktivieren eines gruppierten Indexes wird der Benutzerzugriff auf die zugrunde liegenden Tabellendaten verhindert. Verwenden Sie ALTER INDEX REBUILD oder CREATE INDEX WITH DROP_EXISTING, um einen Index zu aktivieren. Weitere Informationen finden Sie unter Deaktivieren von Indizes und Einschränkungen und Aktivieren von Indizes und Einschränkungen.

REORGANIZE bei einem Rowstore-Index

Gibt für Rowstore-Indizes an, dass die Indexblattebene neu organisiert wird. Für den REORGANIZE-Vorgang gilt:

• Wird immer online ausgeführt. Dies bedeutet, dass keine blockierenden Langzeitsperren für Tabellen aufrechterhalten werden und dass während der ALTER INDEX REORGANIZE-Transaktion Abfragen oder Updates der zugrunde liegenden Tabelle fortgesetzt werden können.
• Nicht zulässig für einen deaktivierten Index
• Nicht zulässig, wenn ALLOW_PAGE_LOCKS auf OFF festgelegt ist
• Es wird kein Rollback durchgeführt, wenn er innerhalb einer Transaktion ausgeführt wird, für die ein Rollback durchgeführt wird.

Weitere Informationen finden Sie unter Neuorganisieren und Neuerstellen von Indizes.

REORGANIZE WITH ( LOB_COMPACTION = { ON | OFF } )

Gilt für Rowstore-Indizes.

LOB_COMPACTION = ON

• Gibt an, dass alle Seiten komprimiert werden, die Daten der folgenden LOB-Datentypen (Large Objects) enthalten: image, text, ntext, varchar(max), nvarchar(max), varbinary(max) und xml. Durch das Komprimieren dieser Daten kann die Datenmenge auf der Festplatte verringert werden.
• Bei einem gruppierten Index werden dadurch alle LOB-Spalten komprimiert, die in der Tabelle enthalten sind.
• Bei einem nicht gruppierten Index werden alle LOB-Spalten komprimiert, die (eingeschlossene) Nichtschlüsselspalten im Index sind.
• Mit REORGANIZE ALL wird LOB_COMPACTION für alle Indizes ausgeführt. Bei jedem Index werden alle LOB-Spalten im gruppierten Index, in der zugrunde liegenden Tabelle oder in eingeschlossenen Spalten in einem nicht gruppierten Index komprimiert.

LOB_COMPACTION = OFF

• Seiten, die LOB-Daten enthalten, werden nicht komprimiert.
• Die Einstellung OFF hat keine Auswirkungen auf einen Heap.

REORGANIZE bei einem Columnstore-Index

Bei Columnstore-Indizes wird durch REORGANIZE jede CLOSED-Deltazeilengruppe im Columnstore als komprimierte Zeilengruppe komprimiert. Der REORGANIZE-Vorgang wird immer online durchgeführt. Dies bedeutet, dass keine blockierenden Langzeitsperren für Tabellen aufrechterhalten werden und dass während der ALTER INDEX REORGANIZE-Transaktion Abfragen oder Updates der zugrunde liegenden Tabelle fortgesetzt werden können. Weitere Informationen finden Sie unter Neuorganisieren und Neuerstellen von Indizes.

• REORGANIZE ist für das Verschieben von CLOSED-Delta-Zeilengruppen in komprimierte Zeilengruppen nicht erforderlich. Der im Hintergrund ausgeführte Tupelverschiebungsvorgang (TM, Tuple Mover) wird in bestimmten Zeitabständen reaktiviert, um CLOSED-Deltazeilengruppen zu komprimieren. Es wird empfohlen, REORGANIZE zu verwenden, wenn TM im Rückstand ist. Mit REORGANIZE können Zeilengruppen aggressiver komprimiert werden.
• Informationen zum Komprimieren aller OPEN- und CLOSED-Zeilengruppen finden Sie unter der REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS)-Option in diesem Abschnitt.

Bei Columnstore-Indizes in SQL Server (ab SQL Server 2016 (13.x)) und Azure SQL-Datenbank werden mit REORGANIZE die folgenden Optimierungen für eine zusätzliche Defragmentierung online ausgeführt:

• Es werden Zeilen physisch aus der Zeilengruppe entfernt, wenn mindestens 10 % der Zeilen logisch gelöscht wurden. Die gelöschten Bytes werden auf den physischen Medien freigegeben. Bei einer komprimierten Zeilengruppe mit 1 Million Zeilen, in der beispielsweise 100.000 Zeilen gelöscht wurden, werden von SQL Server die gelöschten Zeilen entfernt, und die Zeilengruppe wird mit 900.000 Zeilen neu komprimiert. Durch das Entfernen gelöschter Zeilen wird Speicherplatz eingespart.

• Eine oder mehrere komprimierte Zeilengruppen werden kombiniert, um die Anzahl der Zeilen pro Zeilengruppe auf maximal 1.048.576 Zeilen zu erhöhen. Bei einem Massenexport von fünf Batches mit 102.400 Zeilen erhalten Sie beispielsweise fünf komprimierte Zeilengruppen. Wenn Sie REORGANIZE ausführen, werden diese Zeilengruppen in eine komprimierte Zeilengruppe mit 512.000 Zeilen zusammengeführt. Dies setzt voraus, dass keine Wörterbuchumfangsbegrenzungen oder Arbeitsspeichereinschränkungen vorhanden sind.

• Zeilengruppen, in denen mindestens 10 % der Zeilen logisch gelöscht wurden, versucht SQL Server mit einer oder mehreren Zeilengruppen zu kombinieren. Beispiel: Zeilengruppe 1 ist mit 500.000 Zeilen komprimiert und Zeilengruppe 21 mit dem Maximalwert von 1.048.576 Zeilen. In Zeilengruppe 21 wurden 60 % der Zeilen gelöscht, wodurch noch 409.830 Zeilen vorhanden sind. In SQL Server werden diese beiden Zeilengruppen vorzugsweise kombiniert, um eine neue Zeilengruppe mit 909.830 Zeilen zu komprimieren.

REORGANIZE WITH ( COMPRESS_ALL_ROW_GROUPS = { ON | OFF } )

Gilt für Columnstore-Indizes.

Anwendungsbereich: SQL Server (ab SQL Server 2016 (13.x)) und Azure SQL-Datenbank

Mit COMPRESS_ALL_ROW_GROUPS kann die Übernahme von OPEN- oder CLOSED-Delta-Zeilengruppen in den Columnstore erzwungen werden. Mit dieser Option ist es nicht notwendig, den Columnstore-Index zum Leeren der Delta-Zeilengruppe neu zu erstellen. Damit sowie durch die anderen Defragmentierungsfeatures zum Entfernen und Zusammenfügen von Zeilengruppen ist es in den meisten Fällen nicht mehr erforderlich, den Index neu zu erstellen.

• Mit ON wird das Einfügen aller Zeilengruppen in den Columnstore erzwungen, unabhängig von ihrer Größe und ihrem Status (CLOSED oder OPEN).
• Mit der Einstellung OFF wird die Übernahme aller CLOSED-Zeilengruppen in den Columnstore erzwungen.

Weitere Informationen finden Sie unter Neuorganisieren und Neuerstellen von Indizes.

SET ( <set_index option> [ ,... n] )

Gibt Indexoptionen ohne das Neuerstellen oder Neuorganisieren des Indexes an. SET kann für einen deaktivierten Index nicht angegeben werden.

PAD_INDEX = { ON | OFF }

Gibt die Auffüllung von Indizes an. Der Standardwert ist OFF.

EIN

Der Prozentsatz des mit FILLFACTOR angegebenen freien Speicherplatzes wird für die Zwischenebenenseiten des Index angewendet. Wenn FILLFACTOR nicht zum selben Zeitpunkt angegeben wird, zu dem PAD_INDEX auf ON festgelegt wird, wird der in sys.indexes gespeicherte Füllfaktorwert verwendet.

OFF oder fillfactor ist nicht angegeben.

Die Zwischenebenenseiten werden nahezu vollständig gefüllt. Dabei bleibt genügend Platz für mindestens eine Zeile der maximal zulässigen Größe eines Indexes erhalten. Dies erfolgt auf der Grundlage des Schlüsselsatzes in den Zwischenseiten.

Weitere Informationen finden Sie unter CREATE INDEX (Transact-SQL).

FILLFACTOR = fillfactor

Gibt einen Prozentwert an, der dem Füllfaktor entspricht. Dieser Faktor legt fest, wie weit die Datenbank-Engine die Blattebene jeder Indexseite während der Indexerstellung oder -änderung auffüllen soll. Der Wert für fillfactor muss ein ganzzahliger Wert zwischen 1 und 100 sein. Die Standardeinstellung ist 0. Die Füllfaktorwerte 0 und 100 sind in jeder Hinsicht identisch.

Eine explizite FILLFACTOR-Einstellung gilt nur bei der erstmaligen Erstellung oder bei der Neuerstellung des Index. Die Datenbank-Engine hält den angegebenen Prozentsatz des Speicherplatzes auf den Seiten nicht dynamisch frei. Weitere Informationen finden Sie unter CREATE INDEX (Transact-SQL).

Verwenden Sie zum Anzeigen der Füllfaktoreinstellung fill_factor in sys.indexes.

SORT_IN_TEMPDB = { ON | OFF }

Gibt an, ob die Ergebnisse der Sortierung in tempdb gespeichert werden sollen. Außer für Azure SQL-Datenbank Hyperscale ist der Standardwert OFF. Für alle Indexerstellungsvorgänge in Hyperscale ist SORT_IN_TEMPDB unabhängig von der angegebenen Option immer auf ON festgelegt, sofern nicht die fortsetzbare Indexneuerstellung verwendet wird.

EIN
Die Zwischenergebnisse von Sortierungen, mit denen der Index erstellt wird, werden in tempdb gespeichert. Wenn sich tempdb auf einem anderen Datenträgersatz befindet als die Benutzerdatenbank, kann die zum Erstellen eines Index erforderliche Zeit reduziert werden. Sie erhöht jedoch den Betrag an Speicherplatz, der während der Indexerstellung verwendet wird.

OFF
Die Zwischenergebnisse der Sortierung werden in derselben Datenbank gespeichert wie der Index.

Wenn ein Sortiervorgang nicht erforderlich ist oder im Arbeitsspeicher ausgeführt werden kann, wird die SORT_IN_TEMPDB-Option ignoriert.

Weitere Informationen finden Sie unter SORT_IN_TEMPDB-Option für Indizes.

IGNORE_DUP_KEY = { ON | OFF }

Gibt die Fehlermeldung an, wenn ein Einfügevorgang versucht, doppelte Schlüsselwerte in einen eindeutigen Index einzufügen. Die IGNORE_DUP_KEY-Option gilt nur für Einfügevorgänge nach dem Erstellen oder Neuerstellen des Index. Der Standardwert ist OFF.

EIN
Eine Warnmeldung wird ausgegeben, wenn doppelte Schlüsselwerte in einen eindeutigen Index eingefügt werden. Es schlagen nur die Zeilen fehl, die gegen die Eindeutigkeitseinschränkung verstoßen.

OFF
Eine Fehlermeldung wird ausgegeben, wenn doppelte Schlüsselwerte in einen eindeutigen Index eingefügt werden. Für den gesamten INSERT-Vorgang wird ein Rollback ausgeführt.

IGNORE_DUP_KEY kann für Indizes, die für eine Sicht, nicht eindeutige Indizes, XML-Indizes, räumliche Indizes und gefilterte Indizes erstellt werden, nicht auf ON festgelegt werden.

Verwenden Sie sys.indexes, um IGNORE_DUP_KEY anzuzeigen.

In abwärtskompatibler Syntax entspricht WITH IGNORE_DUP_KEYWITH IGNORE_DUP_KEY = ON.

STATISTICS_NORECOMPUTE = { ON | OFF}

Gibt an, ob Verteilungsstatistiken neu berechnet werden. Der Standardwert ist OFF.

EIN
Veraltete Statistiken werden nicht automatisch neu berechnet.

OFF
Die automatischen Updates der Statistiken sind aktiviert.

Wenn Sie die automatische Aktualisierung von Statistiken wiederherstellen möchten, legen Sie STATISTICS_NORECOMPUTE auf OFF fest oder führen Sie die UPDATE STATISTICS-Anweisung ohne die NORECOMPUTE-Klausel aus.

STATISTICS_INCREMENTAL = { ON | OFF }

Gilt für: SQL Server (ab SQL Server 2014 (12.x)) und Azure SQL-Datenbank

Bei ON wird die Statistik pro Partition erstellt. Bei OFF wird die Statistikstruktur gelöscht, und SQL Server berechnet die Statistiken erneut. Der Standardwert ist OFF.

Wenn Statistiken pro Partition nicht unterstützt werden, wird die Option ignoriert und eine Warnung generiert. Inkrementelle Statistiken werden für folgende Statistiktypen nicht unterstützt:

• Statistiken, die mit Indizes erstellt wurden, die über keine Partitionsausrichtung mit der Basistabelle verfügen
• Statistiken, die für lesbare sekundäre Datenbanken von Verfügbarkeitsgruppen erstellt wurden
• Statistiken, die für schreibgeschützte Datenbanken erstellt wurden
• Statistiken, die für gefilterte Indizes erstellt wurden
• Statistiken, die für Sichten erstellt wurden
• Statistiken, die für interne Tabellen erstellt wurden
• Statistiken, die mit räumlichen Indizes oder XML-Indizes erstellt wurden

ONLINE = { ON | OFF } <wie für rebuild_index_option>

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.

Bei XML-Indizes oder räumlichen Indizes wird nur ONLINE = OFF unterstützt, und wenn ONLINE auf ON festgelegt wird, wird ein Fehler ausgelöst.

ON
Lang andauernde Tabellensperren werden während des Indexvorgangs nicht aufrechterhalten. Während der Hauptphase des Indexvorgangs wird nur eine beabsichtigte freigegebene Sperre für die Quelltabelle aufrechterhalten. Auf diese Weise können Abfragen oder Updates der zugrunde liegenden Tabelle und Indizes fortgesetzt werden. Zu Beginn des Vorgangs wird das Quellobjekt für sehr kurze Zeit mit einer gemeinsamen Sperre (S) belegt. Am Ende des Vorgangs wird für sehr kurze Zeit eine gemeinsame Sperre (S) für die Quelle aktiviert, wenn ein nicht gruppierter Index erstellt wird. Eine Schemaänderungssperre (Schema Modification, Sch-M) wird aktiviert, wenn ein gruppierter Index online erstellt oder gelöscht wird und wenn ein gruppierter oder nicht gruppierter Index neu erstellt wird. ONLINE kann nicht auf ON festgelegt werden, wenn ein Index für eine lokale temporäre Tabelle erstellt wird.

OFF
Tabellensperren werden während des Indexvorgangs angewandt. Ein Offlineindexvorgang, durch den ein gruppierter, räumlicher oder XML-Index erstellt, neu erstellt oder gelöscht wird bzw. ein nicht gruppierter Index neu erstellt oder gelöscht wird, aktiviert eine Sch-M-Sperre für die Tabelle. Dadurch wird verhindert, dass Benutzer während des Vorgangs auf die zugrunde liegende Tabelle zugreifen können. Ein Offlineindexvorgang, bei dem ein nicht gruppierter Index erstellt wird, aktiviert eine freigegebene Sperre (S) für die Tabelle. Dadurch werden Updates der zugrunde liegenden Tabelle verhindert. Lesevorgänge, wie SELECT-Anweisungen, sind jedoch zulässig.

Weitere Informationen finden Sie unter Ausführen von Onlineindexvorgängen .

Indizes, einschließlich Indizes globaler temporärer Tabellen, können online neu erstellt werden. Es gelten folgende Ausnahmen:

• XML-Index
• Index für eine lokale temp-Tabelle
• Eindeutiger gruppierter Ausgangsindex für eine Sicht
• Columnstore-Indizes
• Gruppierter Index, wenn die zugrunde liegende Tabelle LOB-Datentypen (image, ntext, text) und räumliche Datentypen enthält.
• varchar(max)- und varbinary(max)-Spalten können nicht Teil eines Index sein. In SQL Server (ab SQL Server 2012 (11.x)) und Azure SQL-Datenbank kann ein gruppierter Index mit der Option ONLINE erstellt oder neu erstellt werden, wenn eine Tabelle Spalten vom Typ varchar(max) oder varbinary(max) enthält. Azure SQL-Datenbank lässt die ONLINE-Option nicht zu, wenn die Basistabelle Spalten vom Typ varchar(max) oder varbinary(max) enthält.

Weitere Informationen finden Sie unter Funktionsweise von Onlineindexvorgängen.

Die folgenden XEvents beziehen sich auf ALTER TABLE ... SWITCH PARTITION und die Neuerstellung von Onlineindizes.

• lock_request_priority_state
• process_killed_by_abort_blockers
• ddl_with_wait_at_low_priority

Das vorhandene XEvent progress_report_online_index_operation für Onlineindexvorgänge umfasst partition_number und partition_id.

RESUMABLE = { ON | OFF}

Gilt für: SQL Server (ab SQL Server 2017 (14.x)) und Azure SQL-Datenbank

Gibt an, ob ein Onlineindexvorgang fortsetzbar ist.

EIN
Der Indexvorgang ist fortsetzbar.

OFF
Der Indexvorgang ist nicht fortsetzbar.

MAX_DURATION = time [MINUTEN]; wird mit RESUMABLE = ON verwendet (erfordert ONLINE = ON)

Gilt für: SQL Server (ab SQL Server 2017 (14.x)) und Azure SQL-Datenbank

Gibt die Zeitspanne an (als ganzzahligen Wert in Minuten), in der ein fortsetzbarer Onlineindexvorgang ausgeführt wird, bevor er angehalten wird.

ALLOW_ROW_LOCKS = { ON | OFF }

Gibt an, ob Zeilensperren zulässig sind. Der Standardwert ist ON.

EIN
Zeilensperren sind beim Zugriff auf den Index zulässig. Das Datenbank-Engine bestimmt, wann Zeilensperren verwendet werden.

OFF
Zeilensperren werden nicht verwendet.

ALLOW_PAGE_LOCKS = { ON | OFF }

Gibt an, ob Seitensperren zulässig sind. Der Standardwert ist ON.

EIN
Seitensperren sind beim Zugriff auf den Index zulässig. Das Datenbank-Engine bestimmt, wann Seitensperren verwendet werden.

OFF
Seitensperren werden nicht verwendet.

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }

Gilt für: SQL Server (ab SQL Server 2019 (15.x)) und Azure SQL-Datenbank

Gibt an, ob der Konflikt beim Einfügen der letzten Seite optimiert werden soll. Der Standardwert ist OFF. Weitere Informationen finden Sie unter Sequenzielle Schlüssel.

MAXDOP = max_degree_of_parallelism

Überschreibt die Konfigurationsoption Max. Grad an Parallelität während des Indexvorgangs. Weitere Informationen finden Sie unter Konfigurieren der Serverkonfigurationsoption Max. Grad an Parallelität. Sie können mit MAXDOP die Anzahl der Prozessoren begrenzen, die bei der Ausführung paralleler Pläne verwendet werden. Maximal sind 64 Prozessoren zulässig.

max_degree_of_parallelism kann folgende Werte haben:

1
Unterdrückt das Generieren paralleler Pläne.

>1
Begrenzt die Höchstzahl von Prozessoren in einem parallelen Indexvorgang auf die angegebene Zahl

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

Weitere Informationen finden Sie unter Konfigurieren von Parallelindexvorgängen.

COMPRESSION_DELAY = { 0 | duration [Minuten] }

Anwendungsbereich: SQL Server (ab SQL Server 2016 (13.x))

DELAY gibt bei einer datenträgerbasierten Tabelle die minimale Anzahl von Minuten an, die eine Deltazeilengruppe im Zustand CLOSED in der Delta-Zeilengruppe verbringen muss, bevor SQL Server sie in die komprimierte Zeilengruppe komprimieren kann. Da Einfügungs- und Aktualisierungszeiten in datenträgerbasierten Tabellen nicht für einzelne Zeilen nachverfolgt werden, wird die Verzögerung in SQL Server auf Delta-Zeilengruppen im CLOSED-Status angewendet.

Die Standardeinstellung beträgt 0 Minuten.

Empfehlungen zur Verwendung von COMPRESSION_DELAY finden Sie unter Erste Schritte mit Columnstore für operative Echtzeitanalyse.

DATA_COMPRESSION

Gibt die Datenkomprimierungsoption für den angegebenen Index, die Partitionsnummer oder den Bereich von Partitionen an. Die folgenden Optionen sind verfügbar:

Keine
Der Index oder die angegebenen Partitionen werden nicht komprimiert. Dies gilt nicht für Columnstore-Indizes.

ROW
Der Index oder die angegebenen Partitionen werden mit Zeilenkomprimierung komprimiert. Dies gilt nicht für Columnstore-Indizes.

PAGE
Der Index oder die angegebenen Partitionen werden mit Seitenkomprimierung komprimiert. Dies gilt nicht für Columnstore-Indizes.

COLUMNSTORE

Gilt für: SQL Server (ab SQL Server 2014 (12.x)) und Azure SQL-Datenbank

Gilt nur für columnstore-Indizes, einschließlich nicht gruppierter und gruppierter columnstore-Indizes. COLUMNSTORE gibt an, dass der Index oder angegebene Partitionen, die mit der COLUMNSTORE_ARCHIVE-Option komprimiert wurden, dekomprimiert werden sollen. Nachdem die Daten wiederhergestellt wurden, sind sie weiterhin mit der columnstore-Komprimierung komprimiert, die für alle columnstore-Indizes verwendet wird.

COLUMNSTORE_ARCHIVE

Gilt für: SQL Server (ab SQL Server 2014 (12.x)) und Azure SQL-Datenbank

Gilt nur für columnstore-Indizes, einschließlich nicht gruppierter und gruppierter columnstore-Indizes. Durch COLUMNSTORE_ARCHIVE wird die angegebene Partition weiter in eine geringere Größe komprimiert. Dies empfiehlt sich bei der Archivierung und in Situationen, in denen es auf eine geringere Speichergröße und nicht auf den zusätzlichen Zeitaufwand für das Speichern und Abrufen ankommt.

Weitere Informationen zur Datenkomprimierung finden Sie unter Datenkomprimierung.

XML_COMPRESSION

Gilt für: SQL Server 2022 (16.x) und höher, Azure SQL-Datenbank, Azure SQL Managed Instance

Gibt die XML-Komprimierungsoption für den angegebenen Index an, der mindestens eine Spalte vom XML-Datentyp enthält. Die folgenden Optionen sind verfügbar:

EIN
Der Index oder die angegebenen Partitionen werden mit der XML-Komprimierung komprimiert.

OFF
Der Index oder die angegebenen Partitionen werden nicht komprimiert.

ON PARTITIONS ( { <partition_number_expression> | <range> } [,...n] )

Gibt die Partitionen an, auf die die Einstellungen DATA_COMPRESSION oder XML_COMPRESSION angewendet werden. Wenn der Index nicht partitioniert ist, erzeugt das ON PARTITIONS-Argument einen Fehler. Wenn die ON PARTITIONS-Klausel nicht angegeben wird, gilt die DATA_COMPRESSION- oder XML_COMPRESSION-Option für alle Partitionen eines partitionierten Indexes.

<partition_number_expression> kann wie folgt angegeben werden:

• Geben Sie die Nummer für eine Partition an, beispielsweise: ON PARTITIONS (2).
• Geben Sie die Partitionsnummern für mehrere einzelne Partitionen durch Kommas getrennt an, beispielsweise: ON PARTITIONS (1, 5).
• Geben Sie sowohl Bereiche als auch einzelne Partitionen an: ON PARTITIONS (2, 4, 6 TO 8).

Für <range> können durch das Wort „TO“ getrennte Partitionsnummern angegeben werden, z. B. ON PARTITIONS (6 TO 8).

Wenn Sie für verschiedene Partitionen unterschiedliche Datenkomprimierungstypen festlegen möchten, geben Sie die Option DATA_COMPRESSION mehrmals an, beispielsweise:

REBUILD WITH
(
DATA_COMPRESSION = NONE ON PARTITIONS (1),
DATA_COMPRESSION = ROW ON PARTITIONS (2, 4, 6 TO 8),
DATA_COMPRESSION = PAGE ON PARTITIONS (3, 5)
);

Sie können die Option XML_COMPRESSION auch mehrmals angeben. Beispiel:

REBUILD WITH
(
  XML_COMPRESSION = OFF ON PARTITIONS (1),
  XML_COMPRESSION = ON ON PARTITIONS (2, 4, 6 TO 8),
  XML_COMPRESSION = OFF ON PARTITIONS (3, 5)
);

ONLINE = { ON | OFF } <wie für single_partition_rebuild_index_option>

Gibt an, ob ein Index oder eine Indexpartition einer zugrunde liegenden Tabelle online oder offline neu erstellt werden kann. Wenn REBUILD ... ONLINE = ON ausgeführt wird, sind die Daten in dieser Tabelle für Abfragen und Datenänderungen während des Indexvorgangs verfügbar. Der Standardwert ist OFF.

EIN
Lang andauernde Tabellensperren werden während des Indexvorgangs nicht aufrechterhalten. Während der Hauptphase des Indexvorgangs wird nur eine beabsichtigte freigegebene Sperre für die Quelltabelle aufrechterhalten. Eine Schemastabilitätssperre (Schema Stability, Sch-S) für die Tabelle ist erforderlich, wenn die Indexneuerstellung gestartet wird, und eine Schemaänderungssperre (Schema Modification, Sch-M) für die Tabelle ist am Ende der Onlineneuerstellung des Index erforderlich. Obwohl beide Metadatensperren von kurzer Dauer sind, muss insbesondere die Sch-M-Sperre auf den Abschluss aller blockierenden Transaktionen warten. Während der Wartezeit sperrt die Sch-M-Sperre alle anderen Transaktionen, die an dieser Sperre warten, wenn sie auf die gleiche Tabelle zugreifen.

OFF
Tabellensperren werden während des Indexvorgangs angewandt. Dadurch wird verhindert, dass Benutzer während des Vorgangs auf die zugrunde liegende Tabelle zugreifen können.

RESUME

Gilt für: SQL Server (ab SQL Server 2017 (14.x)) und Azure SQL-Datenbank

Fortsetzen eines Indexvorgangs, der manuell oder aufgrund eines Fehlers angehalten wurde.

MAX_DURATION mit RESUMABLE = ON

Die Zeitspanne (als ganzzahliger Wert in Minuten), die ein fortsetzbarer Onlineindexvorgang ausgeführt wird, nachdem er fortgesetzt wurde. Nach Ablauf dieser Zeitspanne wird der fortsetzbare Vorgang angehalten, falls er noch ausgeführt wird.

WAIT_AT_LOW_PRIORITY mit RESUMABLE = ON und ONLINE = ON.

Beim Fortsetzen einer Onlineindexneuerstellung nach dem Anhalten muss auf blockierende Vorgänge für diese Tabelle gewartet werden. WAIT_AT_LOW_PRIORITY gibt an, dass der Onlineneuerstellungsvorgang für den Index auf Sperren mit niedriger Priorität wartet und die weitere Ausführung anderer Vorgänge ermöglicht, während der Onlineerstellungsvorgang für den Index wartet. Das Weglassen der WAIT_AT_LOW_PRIORITY-Option entspricht WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE). Weitere Informationen finden Sie unter WAIT_AT_LOW_PRIORITY.

PAUSE

Gilt für: SQL Server (ab SQL Server 2017 (14.x)) und Azure SQL-Datenbank

Anhalten eines fortsetzbaren Onlineneuerstellungsvorgangs für einen Index.

ABORT

Gilt für: SQL Server (ab SQL Server 2017 (14.x)) und Azure SQL-Datenbank

Abbrechen eines ausgeführten oder angehaltenen Indexvorgangs, der als fortsetzbar deklariert wurde. Zum Beenden eines fortsetzbaren Indexneuerstellungsvorgangs müssen Sie explizit einen ABORT-Befehl ausführen. Durch das Auftreten eines Fehlers oder durch Anhalten eines fortsetzbaren Indexvorgangs wird dessen Ausführung nicht beendet. Der Vorgang befindet sich stattdessen in einem unbestimmten Pausenzustand.

Bemerkungen

ALTER INDEX kann nicht verwendet werden, um einen Index neu zu partitionieren oder ihn in eine andere Dateigruppe zu verschieben. Das Ändern der Indexdefinition, z. B. das Hinzufügen oder Löschen von Spalten oder das Ändern der Spaltenreihenfolge, ist mit dieser Anweisung nicht möglich. Verwenden Sie CREATE INDEX mit der DROP_EXISTING-Klausel, um diese Vorgänge auszuführen.

Wenn eine Option nicht explizit angegeben ist, wird die aktuelle Einstellung angewandt. Wenn in der REBUILD-Klausel beispielsweise keine FILLFACTOR-Einstellung angegeben ist, wird der im Systemkatalog gespeicherte Füllfaktorwert während der Neuerstellung verwendet. Verwenden Sie zum Anzeigen der aktuellen Indexoptionseinstellungen sys.indexes.

Die Werte für ONLINE, MAXDOP und SORT_IN_TEMPDB werden nicht im Systemkatalog gespeichert. Der Standardwert der Option wird verwendet, sofern die Option nicht in der Indexanweisung angegeben ist.

Auf Mehrprozessorcomputern werden für ALTER INDEX REBUILD, wie bei anderen Abfragen auch, automatisch weitere Prozessoren verwendet, um die Scan- und Sortierungsvorgänge auszuführen, die mit einem Ändern des Index verbunden sind. Wenn Sie ALTER INDEX REORGANIZE mit oder ohne LOB_COMPACTION ausführen, entspricht der Wert von Max. Grad an Parallelität einem einzelnen Threadvorgang. Weitere Informationen finden Sie unter Konfigurieren von Parallelindexvorgängen.

Neuerstellen von Indizes

Beim Neuerstellen eines Indexes wird der Index gelöscht und neu erstellt. Bei diesem Vorgang wird die Fragmentierung entfernt, Speicherplatz wird freigegeben, indem die Seiten auf der Grundlage der angegebenen oder vorhandenen Füllfaktoreinstellung komprimiert werden, und die Indexzeilen werden in aufeinanderfolgenden Seiten neu geordnet. Wenn ALL angegeben ist, werden alle Indizes der Tabelle in einer einzelnen Transaktion gelöscht und neu erstellt. Fremdschlüsseleinschränkungen müssen nicht im Voraus gelöscht werden. Wenn Indizes mit mindestens 128 Blöcken neu erstellt werden, verzögert das Datenbank-Engine die tatsächlichen aufgehobenen Seitenzuordnungen sowie deren zugeordnete Sperren, bis für die Transaktion ein Commit ausgeführt wird.

Weitere Informationen finden Sie unter Neuorganisieren und Neuerstellen von Indizes.

Neuorganisieren von Indizes

Das Neuorganisieren eines Indexes beansprucht minimale Systemressourcen. Dabei wird die Blattebene von gruppierten und nicht gruppierten Indizes in Tabellen und Sichten defragmentiert, indem die Blattebenenseiten physisch neu geordnet werden, damit sie mit der logischen Reihenfolge der Blattknoten von links nach rechts übereinstimmen. Durch das Neuorganisieren werden die Indexseiten auch komprimiert. Die Komprimierung basiert auf dem vorhandenen Füllfaktorwert.

Wenn ALL angegeben ist, werden relationale Indizes, sowohl gruppierte als auch nicht gruppierte, und XML-Indizes der Tabelle neu organisiert. Bei Angabe von ALL gelten einige Einschränkungen, die Sie in der Definition für ALL in diesem Artikel im Abschnitt „Argumente“ finden.

Weitere Informationen finden Sie unter Neuorganisieren und Neuerstellen von Indizes.

Deaktivieren von Indizes

Durch das Deaktivieren eines Indexes wird der Benutzerzugriff auf den Index sowie auf die zugrunde liegenden Tabellendaten gruppierter Indizes verhindert. Die Indexdefinition bleibt im Systemkatalog erhalten. Beim Deaktivieren eines nicht gruppierten oder gruppierten Indexes in einer Sicht werden die Indexdaten physisch gelöscht. Durch das Deaktivieren eines gruppierten Indexes wird der Benutzerzugriff auf die Daten verhindert; die Daten bleiben jedoch in der B-Struktur unverwaltet, bis der Index gelöscht oder neu erstellt wird. Führen Sie eine Abfrage für die is_disabled-Spalte in der sys.indexes-Katalogsicht aus, um den Status eines aktivierten oder deaktivierten Index anzuzeigen.

Befindet sich eine Tabelle in einer Transaktionsreplikationsveröffentlichung, können die Indizes, die mit Primärschlüsselspalten verknüpft sind, nicht deaktiviert werden, weil diese Indizes von der Replikation benötigt werden. Wenn Sie einen Index deaktivieren möchten, müssen Sie zuerst die Tabelle aus der Veröffentlichung löschen. Weitere Informationen finden Sie unter Veröffentlichen von Daten und Datenbankobjekten.

Verwenden Sie die ALTER INDEX REBUILD-Anweisung oder die CREATE INDEX WITH DROP_EXISTING-Anweisung, um den Index zu aktivieren. Das Neuerstellen eines deaktivierten gruppierten Index kann nicht durchgeführt werden, wenn die ONLINE-Option auf ON festgelegt ist. Weitere Informationen finden Sie unter Deaktivieren von Indizes und Einschränkungen.

Einstellungsoptionen

Sie können die Optionen ALLOW_ROW_LOCKS, ALLOW_PAGE_LOCKS, OPTIMIZE_FOR_SEQUENTIAL_KEY, IGNORE_DUP_KEY und STATISTICS_NORECOMPUTE für einen angegebenen Index festlegen, ohne diesen Index neu zu erstellen oder neu zu organisieren. Die geänderten Werte werden sofort auf den Index angewendet. Verwenden Sie zum Anzeigen dieser Einstellungen sys.indexes. Weitere Informationen finden Sie unter Festlegen von Indexoptionen.

Zeilen- und Seitensperren (Optionen)

Wenn ALLOW_ROW_LOCKS = ON und ALLOW_PAGE_LOCK = ON angegeben sind, sind Sperren auf Zeilen-, Seiten- und Tabellenebene zulässig, wenn auf den Index zugegriffen wird. Das Datenbank-Engine wählt die geeignete Sperre aus und kann die Sperre von einer Zeilen- oder Seitensperre auf eine Tabellensperre ausweiten.

Wenn ALLOW_ROW_LOCKS = OFF und ALLOW_PAGE_LOCK = OFF angegeben sind, ist nur eine Sperre auf Tabellenebene zulässig, wenn auf den Index zugegriffen wird.

Wenn beim Festlegen der Optionen für Zeilen- oder Seitensperren ALL angegeben ist, werden die Einstellungen auf alle Indizes angewendet. Wenn es sich bei der zugrunde liegenden Tabelle um einen Heap handelt, werden die Einstellungen folgendermaßen angewendet:

Option	Details
ALLOW_ROW_LOCKS = ON oder OFF	Für den Heap und alle zugeordneten nicht gruppierten Indizes.
ALLOW_PAGE_LOCKS = ON	Für den Heap und alle zugeordneten nicht gruppierten Indizes.
ALLOW_PAGE_LOCKS = OFF	Vollständig für die nicht gruppierten Indizes. Dies bedeutet, dass für die nicht gruppierten Indizes keine Seitensperren zulässig sind. Beim Heap sind nur gemeinsame Sperren (S, Shared), Updatesperren (U, Update) und exklusive Sperren (X, Exclusive) für die Seite unzulässig. Das Datenbank-Engine kann weiterhin eine beabsichtigte Seitensperre (IS, IU oder IX) für interne Zwecke abrufen.

Online-Indexvorgänge

Wenn Sie einen Index neu erstellen, und die ONLINE-Option ist auf ON festgelegt, sind die zugrunde liegenden Objekte, die Tabellen und zugeordneten Indizes für Abfragen und Datenänderungen verfügbar. Sie können auch einen Teil eines Indexes online neu erstellen, der sich in einer einzelnen Partition befindet. Exklusive Tabellensperren werden während des Änderungsprozesses nur für einen kurzen Zeitraum aufrechterhalten.

Das Neuorganisieren eines Indexes wird stets online durchgeführt. Bei dem Prozess werden Sperren nicht dauerhaft aufrechterhalten, daher werden laufende Abfragen oder Updates nicht blockiert.

Gleichzeitige Onlineindexvorgänge können in derselben Tabelle oder Tabellenpartition nur bei den folgenden Aktionen ausgeführt werden:

• Erstellen mehrerer nicht gruppierter Indizes.
• Neuorganisieren unterschiedlicher Indizes in derselben Tabelle.
• Neuorganisieren unterschiedlicher Indizes während der Neuerstellung von nicht überlappenden Indizes derselben Tabelle.

Alle anderen gleichzeitig durchgeführten Onlineindexvorgänge erzeugen einen Fehler. Sie können beispielsweise nicht zwei oder mehr Indizes zur gleichen Zeit für dieselbe Tabelle neu erstellen bzw. beim Neuerstellen eines vorhandenen Index keinen neuen Index für dieselbe Tabelle erstellen.

Weitere Informationen finden Sie unter Ausführen von Onlineindexvorgängen .

Fortsetzbare Indexvorgänge

Gilt für: SQL Server (ab SQL Server 2017 (14.x)) und Azure SQL-Datenbank

Eine Onlineneuerstellung eines Indexes wird mit der RESUMABLE = ON-Option als fortsetzbar angegeben.

• Die RESUMABLE-Option wird in den Metadaten nicht für einen bestimmten Index beibehalten und gilt nur für die Dauer der aktuellen DDL-Anweisung. Daher muss die RESUMABLE = ON-Klausel explizit angegeben werden, wenn Fortsetzbarkeit aktiviert werden soll.

• Die MAX_DURATION-Option wird für die RESUMABLE = ON- oder die low_priority_lock_wait-Option unterstützt.

  • MAX_DURATION gibt bei der RESUMABLE-Option das Zeitintervall an, in dem ein Index neu erstellt wird. Sobald dieses Zeitintervall beendet ist, wird die Indexneuerstellung entweder angehalten oder in ihrer Ausführung beendet. Der Benutzer entscheidet, wann die Neuerstellung eines angehaltenen Index fortgesetzt werden kann. Die Zeitspanne (in Minuten) für MAX_DURATION muss größer als 0 Minuten und kleiner oder gleich einer Woche (7 × 24 × 60 = 10080 Minuten) sein. Das lange Anhalten eines Indexvorgangs kann Auswirkungen auf die DML-Leistung für eine bestimmte Tabelle sowie die Datenträgerkapazität der Datenbank haben, da sowohl der ursprüngliche Index als auch der neu erstellte Index Speicherplatz benötigen und während der DML-Vorgänge aktualisiert werden müssen. Wird die MAX_DURATION-Option ausgelassen, dann wird der Indexvorgang bis zum vollständigen Abschluss fortgesetzt oder bis ein Fehler auftritt.
  • Mit der Argumentoption low_priority_lock_wait können Sie entscheiden, wie der Indexvorgang fortgesetzt werden kann, wenn er für die Sch-M-Sperre blockiert wird.

• Durch das erneute Ausführen der ursprünglichen ALTER INDEX REBUILD-Anweisung mit denselben Parametern wird ein angehaltener Indexneuerstellungsvorgang fortgesetzt. Auch durch Ausführen der ALTER INDEX RESUME-Anweisung kann ein angehaltener Indexneuerstellungsvorgang fortgesetzt werden.

• Die Option SORT_IN_TEMPDB = ON wird für einen fortsetzbaren Index nicht unterstützt.

• Der DDL-Befehl mit RESUMABLE = ON kann nicht innerhalb einer expliziten Transaktion ausgeführt werden (kann nicht Teil des BEGIN TRAN ... COMMIT-Blocks sein).

• Nur Indexvorgänge, die angehalten wurden, sind fortsetzbar.

• Beim Fortsetzen eines Indexvorgangs, der angehalten wurde, können Sie den MAXDOP-Wert in einen neuen Wert ändern. Wird MAXDOP beim Fortsetzen eines angehaltenen Indexvorgangs nicht angegeben, wird der letzte MAXDOP-Wert übernommen. Wird die MAXDOP-Option überhaupt nicht für Indexneuerstellungsvorgänge angegeben, wird der Standardwert übernommen.

• Wenn Sie den Indexvorgang sofort anhalten möchten, können Sie den laufenden Befehl beenden (STRG+C) oder den Befehl ALTER INDEX PAUSE oder KILL <session_id> ausführen. Ein angehaltener Befehl kann mit der RESUME-Option fortgesetzt werden.

• Mit dem ABORT-Befehl wird die Sitzung beendet, die die ursprüngliche Indexneuerstellung gehostet hat, und der Indexvorgang wird abgebrochen.

• Für die fortsetzbare Indexneuerstellung werden keine zusätzlichen Ressourcen benötigt, mit Ausnahme von:

  • zusätzlichem Speicherplatz, damit der Index weiter erstellt wird, einschließlich der Zeit, wenn der Index angehalten wird
  • DDL-Status zur Verhinderung von DDL-Änderungen

• Der Cleanup inaktiver Datensätze wird ausgeführt, während der Index angehalten wird. Er wird jedoch angehalten, während der Index ausgeführt wird. Die folgenden Funktionen sind für Indexneuerstellungsvorgänge deaktiviert:

  • Neuerstellen eines deaktivierten Index wird mit RESUMABLE = ON nicht unterstützt
  • ALTER INDEX REBUILD ALL-Befehl
  • ALTER TABLE bei der Indexneuerstellung
  • DDL-Befehl mit RESUMABLE = ON kann nicht innerhalb einer expliziten Transaktion ausgeführt werden (kann nicht Teil des BEGIN TRAN ... COMMIT-Blocks sein)
  • Erstellen eines Index, der mindestens eine berechnete Spalte oder TIMESTAMP-Spalte als Schlüsselspalte besitzt

• Sollte die Basistabelle mindestens eine LOB-Spalte besitzen, dann ist für die Indexneuerstellung eines fortsetzbaren gruppierten Index eine Sch-M-Sperre zu Beginn dieses Vorgangs erforderlich.

WAIT_AT_LOW_PRIORITY bei Onlineindexvorgängen

Gilt für: SQL Server (ab SQL Server 2014 (12.x)) und Azure SQL-Datenbank

Die Syntax low_priority_lock_wait ermöglicht die Angabe des Verhaltens WAIT_AT_LOW_PRIORITY. Die Verwendung von WAIT_AT_LOW_PRIORITY ist nur mit ONLINE = ON möglich.

Um die DDL-Anweisung für eine Onlineindexneuerstellung auszuführen, müssen alle aktiven blockierenden Transaktionen, die für eine bestimmte Tabelle ausgeführt werden, abgeschlossen sein. Wenn die Onlineindexneuerstellung ausgeführt wird, werden alle neuen Transaktionen, die zur Ausführung in dieser Tabelle bereit sind, blockiert. Obwohl die Sperre für die Onlineindexneuerstellung nur kurz dauert, kann das Warten auf den Abschluss aller noch offenen Transaktionen und das Blockieren aller neuen, zu startenden Transaktionen für eine bestimmte Tabelle den Durchsatz beeinträchtigen, eine Verlangsamung oder einen Ausfall der Arbeitsauslastung verursachen und den Zugriff auf die zugrunde liegende Tabelle deutlich einschränken.

Mit der WAIT_AT_LOW_PRIORITY-Option können Datenbankadministrator*innen die Schemastabilitätssperren (Sch-S) und die Schemaänderungssperren (Sch-M), die für die Onlineneuerstellung von Indizes erforderlich sind, verwalten und eine von zwei Optionen auswählen. In beiden Fällen gilt: Sind während der Wartezeit (MAX_DURATION = n [minutes]) keine blockierenden Aktivitäten vorhanden, wird die Onlineindexneuerstellung ohne Wartezeit sofort ausgeführt, und die DDL-Anweisung wird abgeschlossen.

WAIT_AT_LOW_PRIORITY gibt an, dass der Onlineneuerstellungsvorgang für den Index auf Sperren mit niedriger Priorität wartet und die weitere Ausführung anderer Vorgänge ermöglicht, während der Onlineerstellungsvorgang für den Index wartet. Das Weglassen der WAIT AT LOW PRIORITY-Option entspricht WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE).

MAX_DURATION = time [MINUTES]

Die Wartezeit (ein ganzzahliger Wert in Minuten), während der die Sperren der Onlineindexneuerstellung mit niedriger Priorität warten, wenn der DDL-Befehl ausgeführt wird. Wenn der Vorgang während des MAX_DURATION-Zeitraums blockiert wird, wird die angegebene ABORT_AFTER_WAIT-Aktion ausgeführt. MAX_DURATION wird immer in Minuten angegeben, und das Wort MINUTES kann weggelassen werden.

ABORT_AFTER_WAIT = [ NONE | SELF | BLOCKERS ]

Keine
Es wird weiterhin mit normaler (regulärer) Priorität auf die Sperre gewartet.

SELF
Beendet den DDL-Vorgang zur Onlineindexneuerstellung, der derzeit ausgeführt wird, ohne weitere Aktionen auszuführen. Die Option SELF kann nicht verwendet werden, wenn MAX_DURATION auf 0 (null) festgelegt ist.

BLOCKERS
Bricht alle Benutzertransaktionen ab, die den DDL-Vorgang zur Onlineindexneuerstellung blockieren, sodass der Vorgang fortgesetzt werden kann. Die BLOCKERS-Option erfordert, dass der Anmelder über ALTER ANY CONNECTION-Berechtigungen verfügt.

Einschränkungen für räumliche Indizes

Wenn Sie einen räumlichen Index neu erstellen, ist die zugrunde liegende Benutzertabelle während des Indexvorgangs nicht verfügbar, da für den räumlichen Index eine Schemasperre gilt.

Die PRIMARY KEY-Einschränkung der Benutzertabelle kann nicht geändert werden, solange ein räumlicher Index für eine Spalte der betreffenden Tabelle definiert ist. Die PRIMARY KEY-Einschränkung kann erst geändert werden, nachdem alle räumlichen Indizes aus der Tabelle gelöscht wurden. Nach der Änderung der PRIMARY KEY-Einschränkung können die einzelnen räumlichen Indizes neu erstellt werden.

Räumliche Indizes können in einem Neuerstellungsvorgang einer einzelnen Partition nicht angegeben werden. Sie können räumliche Indizes jedoch bei einer vollständigen Neuerstellung der Partition angeben.

Wenn Sie Optionen ändern möchten, die für einen räumlichen Index spezifisch sind (z. B. BOUNDING_BOX oder GRID), können Sie entweder eine CREATE SPATIAL INDEX-Anweisung verwenden, die DROP_EXISTING = ON angibt, oder den räumlichen Index verwerfen und einen neuen Index erstellen. Ein Beispiel finden Sie unter CREATE SPATIAL INDEX (Transact-SQL).

Datenkomprimierung

Weitere Informationen zur Datenkomprimierung finden Sie unter Datenkomprimierung.

Mithilfe der gespeicherten Prozedur sp_estimate_data_compression_savings können Sie einschätzen, wie sich eine Änderung der PAGE- und ROW-Komprimierung auf eine Tabelle, einen Index oder eine Partition auswirkt.

Für partitionierte Indizes gelten die folgenden Einschränkungen:

• Mit ALTER INDEX ALL ... können Sie die Komprimierungseinstellung einer einzelnen Partition nicht ändern, wenn die Tabelle nicht ausgerichtete Indizes aufweist.
• Mit der Syntax ALTER INDEX <index> ... REBUILD PARTITION ... wird die angegebene Partition des Index neu erstellt.
• Mit der Syntax ALTER INDEX <index> ... REBUILD WITH ... werden alle Partitionen des Index neu erstellt.

Statistik

Wenn Sie ALTER INDEX ALL ... für eine Tabelle ausführen, werden nur die Statistiken mit zugeordneten Indizes aktualisiert. Automatische oder manuelle Statistiken, die anstelle eines Index in der Tabelle erstellt wurden, werden nicht aktualisiert.

Berechtigungen

Zum Ausführen von ALTER INDEX benötigen Sie mindestens die ALTER-Berechtigung für die Tabelle oder Ansicht.

Versionshinweise

• SQL-Datenbank verwendet die Optionen „Dateigruppe“ und „Filestream“ nicht.
• Columnstore-Indizes sind erst ab SQL Server 2012 (11.x) verfügbar.
• Fortsetzbare Indexvorgänge sind verfügbar ab SQL Server 2017 (14.x) und Azure SQL-Datenbank.

Einfaches Syntaxbeispiel

ALTER INDEX index1 ON table1 REBUILD;

ALTER INDEX ALL ON table1 REBUILD;

ALTER INDEX ALL ON dbo.table1 REBUILD;

Beispiele: Columnstore-Indizes

Diese Beispiele gelten für Columnstore-Indizes.

A. REORGANIZE-Demo

In diesem Beispiel wird veranschaulicht, wie der Befehl ALTER INDEX REORGANIZE funktioniert. Es wird eine Tabelle mit mehreren Zeilengruppen erstellt, die anschließend mithilfe von REORGANIZE zusammengeführt werden.

-- Create a database
CREATE DATABASE [ columnstore ];
GO

-- Create a rowstore staging table
CREATE TABLE [ staging ] (
     AccountKey              int NOT NULL,
     AccountDescription      nvarchar (50),
     AccountType             nvarchar(50),
     AccountCodeAlternateKey     int
     )

-- Insert 10 million rows into the staging table.
DECLARE @loop int
DECLARE @AccountDescription varchar(50)
DECLARE @AccountKey int
DECLARE @AccountType varchar(50)
DECLARE @AccountCode int

SELECT @loop = 0
BEGIN TRAN
    WHILE (@loop < 300000)
      BEGIN
        SELECT @AccountKey = CAST (RAND()*10000000 as int);
        SELECT @AccountDescription = 'accountdesc ' + CONVERT(varchar(20), @AccountKey);
        SELECT @AccountType = 'AccountType ' + CONVERT(varchar(20), @AccountKey);
        SELECT @AccountCode =  CAST (RAND()*10000000 as int);

        INSERT INTO  staging VALUES (@AccountKey, @AccountDescription, @AccountType, @AccountCode);

        SELECT @loop = @loop + 1;
    END
COMMIT

-- Create a table for the clustered columnstore index

CREATE TABLE cci_target (
     AccountKey              int NOT NULL,
     AccountDescription      nvarchar (50),
     AccountType             nvarchar(50),
     AccountCodeAlternateKey int
     )

-- Convert the table to a clustered columnstore index named inxcci_cci_target;
CREATE CLUSTERED COLUMNSTORE INDEX idxcci_cci_target ON cci_target;

Mit der TABLOCK-Option können Sie Zeilen parallel einfügen. Ab SQL Server 2016 (13.x) können der INSERT INTO-Vorgang und die TABLOCK-Option parallel ausgeführt werden.

INSERT INTO cci_target WITH (TABLOCK)
SELECT TOP 300000 * FROM staging;

Führen Sie diesen Befehl aus, um die OPEN-Delta-Zeilengruppen anzuzeigen. Die Anzahl der Zeilengruppen hängt vom Grad der Parallelität ab.

SELECT *
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id  = object_id('cci_target');

Führen Sie diesen Befehl aus, um die Übernahme aller CLOSED- und OPEN-Zeilengruppen in den Columnstore zu erzwingen.

ALTER INDEX idxcci_cci_target ON cci_target REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

Führen Sie diesen Befehl erneut aus, um kleinere Zeilengruppen in eine komprimierte Zeilengruppe zusammenzuführen.

ALTER INDEX idxcci_cci_target ON cci_target REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

B. Komprimieren von CLOSED-Delta-Zeilengruppen im Columnstore

In diesem Beispiel wird durch die REORGANIZE-Option jede CLOSED-Delta-Zeilengruppe im Columnstore als eine komprimierte Zeilengruppe komprimiert. Dies ist zwar nicht erforderlich, kann aber nützlich sein, wenn CLOSED-Zeilengruppen im TM-Vorgang (Tuple Mover) nicht schnell genug komprimiert werden.

Sie können beide Beispiele in der Beispieldatenbank AdventureWorksDW2022 ausführen.

In diesem Beispiel wird REORGANIZE auf allen Partitionen ausgeführt.

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE;

In diesem Beispiel wird REORGANIZE auf einer bestimmten Partition ausgeführt.

-- REORGANIZE a specific partition
ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE PARTITION = 0;

C. Komprimieren aller OPEN- und CLOSED-Delta-Zeilengruppen im Columnstore

Anwendungsbereich: SQL Server (ab SQL Server 2016 (13.x)) und Azure SQL-Datenbank

Der Befehl REORGANIZE WITH ( COMPRESS_ALL_ROW_GROUPS = ON ) komprimiert jede OPEN- und CLOSED-Deltazeilengruppe im Columnstore als komprimierte Zeilengruppe. Dadurch wird der Deltastore geleert, und es werden alle Zeilen im Columnstore komprimiert. Dies ist insbesondere nach dem Ausführen einer Vielzahl von Einfügevorgängen nützlich, da die Zeilen bei diesen Vorgängen in einer oder mehreren Delta-Zeilengruppen gespeichert werden.

Mit REORGANIZE werden Zeilengruppen bis zur einer maximalen Anzahl von <= 1.024.576 Zeilen kombiniert. Nach dem Komprimieren aller OPEN- und CLOSED-Zeilengruppen werden nicht mehr viele komprimierte Zeilengruppen übrig bleiben, die nur wenige Zeilen enthalten. Bestmöglich gefüllte Zeilengruppen verringern die komprimierte Größe und verbessern die Abfrageleistung.

In den folgenden Beispielen wird die AdventureWorksDW2022-Datenbank verwendet.

In diesem Beispiel werden alle OPEN- und CLOSED-Delta-Zeilengruppen in den Columnstore-Index verschoben.

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

In diesem Beispiel werden alle OPEN- und CLOSED-Delta-Zeilengruppen in den Columnstore-Index für eine bestimmte Partition verschoben.

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE PARTITION = 0 WITH (COMPRESS_ALL_ROW_GROUPS = ON);

D: Online-Defragmentieren eines Columnstore-Index

Gilt nicht für: SQL Server 2012 (11.x) und SQL Server 2014 (12.x).

Ab SQL Server 2016 (13.x) können Sie mit REORGANIZE mehr tun, als Delta-Zeilengruppen im Columnstore zu komprimieren. Sie können auch eine Onlinedefragmentierung durchführen. Zunächst wird die Größe des Columnstores verringert, indem gelöschte Zeilen physisch entfernt werden, wenn mindestens 10 % der Zeilen in einer Zeilengruppe gelöscht wurden. Anschließend werden Zeilengruppen zu größeren Zeilengruppen bis maximal 1.024.576 Zeilen pro Zeilengruppe zusammengeführt. Alle geänderten Zeilengruppen werden erneut komprimiert.

Im folgenden Beispiel wird ein REORGANIZE-Vorgang zur Indexdefragmentierung ausgeführt, indem Zeilen, die logisch aus der Tabelle gelöscht wurden, physisch entfernt und Zeilengruppen zusammengeführt werden.

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE;

E. Neuerstellen eines gruppierten Columnstore-Index im Offline-Modus

Gilt für: SQL Server (ab SQL Server 2012 (11.x))

In diesem Beispiel wird veranschaulicht, wie Sie einen gruppierten Columnstore-Index neu erstellen und die Übernahme aller Delta-Zeilengruppen in den Columnstore erzwingen. Im ersten Schritt wird eine Tabelle FactInternetSales2 in der Datenbank AdventureWorksDW2022 mit einem gruppierten Columnstore-Index vorbereitet, und es werden Daten aus den ersten vier Spalten eingefügt.

CREATE TABLE dbo.FactInternetSales2 (
    ProductKey [int] NOT NULL,
    OrderDateKey [int] NOT NULL,
    DueDateKey [int] NOT NULL,
    ShipDateKey [int] NOT NULL);

CREATE CLUSTERED COLUMNSTORE INDEX cci_FactInternetSales2
ON dbo.FactInternetSales2;

INSERT INTO dbo.FactInternetSales2
SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey
FROM dbo.FactInternetSales;

SELECT * FROM sys.column_store_row_groups;

In den Ergebnissen wird eine OPEN-Zeilengruppe angezeigt. Dies bedeutet, dass SQL Server wartet, bis weitere Zeilen hinzugefügt werden, bevor die Zeilengruppe geschlossen wird und die Daten in den Columnstore verschoben werden. Mit der nächsten Anweisung wird der gruppierte Columnstore-Index neu erstellt und die Übernahme aller Zeilen in den Columnstore erzwungen.

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REBUILD;
SELECT * FROM sys.column_store_row_groups;

Die Ergebnisse der SELECT-Anweisung zeigen, dass die Zeilengruppe als COMPRESSED gekennzeichnet ist. Dies bedeutet, dass die Spaltensegmente der Zeilengruppe jetzt komprimiert und im Columnstore gespeichert sind.

F. Neuerstellen einer Partition eines gruppierten Columnstore-Index im Offline-Modus

Gilt für: SQL Server (ab SQL Server 2012 (11.x))

Verwenden Sie ALTER INDEX REBUILD mit der Partitionsoption, um eine Partition eines großen gruppierten Columnstore-Index neu zu erstellen. In diesem Beispiel wird die Partition 12 neu erstellt. Ab SQL Server 2016 (13.x) sollten Sie REBUILD durch REORGANIZE ersetzen.

ALTER INDEX cci_fact3
ON fact3
REBUILD PARTITION = 12;

G. Ändern eines gruppierten Columnstore-Index zur Verwendung der Archivierungskomprimierung

Gilt nicht für: SQL Server 2012 (11.x)

Mit der Datenkomprimierungsoption COLUMNSTORE_ARCHIVE können Sie die Größe eines gruppierten Columnstore-Index noch weiter verringern. Dies kann bei älteren Daten nützlich sein, die Sie kostengünstiger speichern möchten. Es wird empfohlen, diese Option nur bei selten verwendeten Daten anzuwenden, da der Dekomprimierungsvorgang länger dauert als bei der üblichen COLUMNSTORE-Komprimierung.

Im folgenden Beispiel wird ein gruppierter columnstore-Index für die Verwendung der Archivierungskomprimierung neu erstellt. Anschließend wird gezeigt, wie die Archivierungskomprimierung entfernt wird. Letztendlich wird nur die columnstore-Komprimierung verwendet.

Bereiten Sie zunächst das Beispiel vor, indem Sie eine Tabelle mit einem gruppierten Columnstore-Index erstellen. Komprimieren Sie die Tabelle dann weiter, indem Sie die Archivierungskomprimierung verwenden.

--Prepare the example by creating a table with a clustered columnstore index.
CREATE TABLE SimpleTable (
    ProductKey [int] NOT NULL,
    OrderDateKey [int] NOT NULL,
    DueDateKey [int] NOT NULL,
    ShipDateKey [int] NOT NULL
);

CREATE CLUSTERED INDEX cci_SimpleTable ON SimpleTable (ProductKey);

CREATE CLUSTERED COLUMNSTORE INDEX cci_SimpleTable
ON SimpleTable
WITH (DROP_EXISTING = ON);

--Compress the table further by using archival compression.
ALTER INDEX cci_SimpleTable ON SimpleTable
REBUILD
WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE);
GO

In diesem Beispiel wird die Archivierungskomprimierung entfernt und nur die Columnstore-Komprimierung verwendet.

ALTER INDEX cci_SimpleTable ON SimpleTable
REBUILD
WITH (DATA_COMPRESSION = COLUMNSTORE);
GO

Beispiele: Rowstore-Indizes

A. Neuerstellen eines Indexes

Im folgenden Beispiel wird ein einzelner Index für die Employee-Tabelle der AdventureWorks2022-Datenbank neu erstellt.

ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee REBUILD;

B. Neuerstellen aller Indizes einer Tabelle und Angeben von Optionen

Im folgenden Beispiel wird das Schlüsselwort ALL angegeben. Dadurch werden alle Indizes neu erstellt, die der Tabelle Production.Product in der Datenbank AdventureWorks2022 zugeordnet sind. Es werden drei Optionen angegeben.

ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);

Im folgenden Beispiel werden die ONLINE-Option einschließlich der Option für Sperren mit niedriger Priorität sowie die Zeilenkomprimierungsoption hinzugefügt.

Gilt für: SQL Server (ab SQL Server 2014 (12.x)) und Azure SQL-Datenbank

ALTER INDEX ALL ON Production.Product
REBUILD WITH
(
    FILLFACTOR = 80,
    SORT_IN_TEMPDB = ON,
    STATISTICS_NORECOMPUTE = ON,
    ONLINE = ON ( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 4 MINUTES, ABORT_AFTER_WAIT = BLOCKERS ) ),
    DATA_COMPRESSION = ROW
);

C. Neuorganisieren eines Index mit LOB-Komprimierung

Im folgenden Beispiel wird ein einzelner gruppierter Index in der AdventureWorks2022-Datenbank neu organisiert. Da der Index einen LOB-Datentyp in der Blattebene enthält, komprimiert die Anweisung auch alle Seiten, die die LOB-Daten enthalten. Die Angabe der WITH (LOB_COMPACTION = ON)-Option ist nicht erforderlich, da der Standardwert auf ON festgelegt ist.

ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto REORGANIZE WITH (LOB_COMPACTION = ON);

D: Festlegen von Optionen für einen Index

Im folgenden Beispiel werden mehrere Optionen für den AK_SalesOrderHeader_SalesOrderNumber-Index in der AdventureWorks2022-Datenbank festgelegt.

ALTER INDEX AK_SalesOrderHeader_SalesOrderNumber ON
    Sales.SalesOrderHeader
SET (
    STATISTICS_NORECOMPUTE = ON,
    IGNORE_DUP_KEY = ON,
    ALLOW_PAGE_LOCKS = ON
    ) ;
GO

E. Deaktivieren eines Index

Im folgenden Beispiel wird ein nicht gruppierter Index für die Employee-Tabelle der AdventureWorks2022-Datenbank deaktiviert.

ALTER INDEX IX_Employee_ManagerID ON HumanResources.Employee DISABLE;

F. Deaktivieren von Einschränkungen

Im folgenden Beispiel wird eine PRIMARY KEY-Einschränkung deaktiviert, indem der PRIMARY KEY-Index in der AdventureWorks2022-Datenbank deaktiviert wird. Die FOREIGN KEY-Einschränkung für die zugrunde liegende Tabelle wird automatisch deaktiviert, und eine Warnmeldung wird angezeigt.

ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department DISABLE;

Das Resultset gibt diese Warnmeldung zurück.

Warning: Foreign key 'FK_EmployeeDepartmentHistory_Department_DepartmentID'
on table 'EmployeeDepartmentHistory' referencing table 'Department'
was disabled as a result of disabling the index 'PK_Department_DepartmentID'.

G. Aktivieren von Einschränkungen

Im folgenden Beispiel werden die in Beispiel F deaktivierten PRIMARY KEY- und FOREIGN KEY-Einschränkungen aktiviert.

Die PRIMARY KEY-Einschränkung wird aktiviert, indem der PRIMARY KEY-Index neu erstellt wird.

ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department REBUILD;

Anschließend wird die FOREIGN KEY-Einschränkung aktiviert.

ALTER TABLE HumanResources.EmployeeDepartmentHistory
CHECK CONSTRAINT FK_EmployeeDepartmentHistory_Department_DepartmentID;
GO

H. Neuerstellen eines partitionierten Index

Im folgenden Beispiel wird eine einzelne Partition mit der Partitionsnummer 5 des partitionierten IX_TransactionHistory_TransactionDate-Indexes in der AdventureWorks2022-Datenbank neu erstellt. Partition 5 wird mit ONLINE=ON neu erstellt, und die zehnminütige Wartezeit für die Sperre mit niedriger Priorität gilt für jede einzelne Sperre, die durch die Indexneuerstellung abgerufen wird. Wenn die Sperre während dieser Zeit nicht für die vollständige Neuerstellung des Indexes reicht, wird die Neuerstellungsanweisung selbst aufgrund von ABORT_AFTER_WAIT = SELF abgebrochen.

Gilt für: SQL Server (ab SQL Server 2014 (12.x)) und Azure SQL-Datenbank

-- Verify the partitioned indexes.
SELECT *
FROM sys.dm_db_index_physical_stats (DB_ID(),OBJECT_ID(N'Production.TransactionHistory'), NULL , NULL, NULL);
GO
--Rebuild only partition 5.
ALTER INDEX IX_TransactionHistory_TransactionDate
ON Production.TransactionHistory
REBUILD Partition = 5
   WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 10 minutes, ABORT_AFTER_WAIT = SELF)));
GO

I. Ändern der Komprimierungseinstellung eines Index

Im folgenden Beispiel wird ein Index für eine nicht partitionierte rowstore-Tabelle neu erstellt.

ALTER INDEX IX_INDEX1
ON T1
REBUILD
WITH (DATA_COMPRESSION = PAGE);
GO

J. Ändern der Einstellung eines Indexes mit XML-Komprimierung

Gilt für: SQL Server 2022 (16.x) und höher, Azure SQL-Datenbank, Azure SQL Managed Instance

Im folgenden Beispiel wird ein Index für eine nicht partitionierte rowstore-Tabelle neu erstellt.

ALTER INDEX IX_INDEX1
ON T1
REBUILD
WITH (XML_COMPRESSION = ON);
GO

Weitere Beispiele für die Datenkomprimierung finden Sie unter Datenkomprimierung.

K. Onlineneuerstellung von fortsetzbaren Indizes

Gilt für: SQL Server (ab SQL Server 2017 (14.x)) und Azure SQL-Datenbank

In den folgenden Beispielen wird veranschaulicht, wie fortsetzbare Onlineindizes neu erstellt werden.

1. Führen Sie eine Onlineindexneuerstellung als fortsetzbaren Vorgang mit MAXDOP = 1 aus.

   ALTER INDEX test_idx on test_table REBUILD WITH (ONLINE = ON, MAXDOP = 1, RESUMABLE = ON) ;
   

2. Wenn Sie denselben Befehl ein zweites Mal ausführen (siehe oben), nachdem ein Indexvorgang angehalten wurde, wird der Indexneuerstellungsvorgang automatisch fortgesetzt.

3. Führen Sie eine Onlineindexneuerstellung als fortsetzbaren Vorgang aus, und legen Sie MAX_DURATION auf 240 Minuten fest.

   ALTER INDEX test_idx on test_table REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 240) ;
   

4. Halten Sie einen fortsetzbaren Onlineneuerstellungsvorgangs für einen Index an.

   ALTER INDEX test_idx on test_table PAUSE ;
   

5. Setzen Sie eine Onlineindexneuerstellung für eine Indexneuerstellung fort, die als fortsetzbarer Vorgang ausgeführt wurde, und geben Sie für MAXDOP den Wert 4 an.

   ALTER INDEX test_idx on test_table RESUME WITH (MAXDOP = 4) ;
   

6. Setzen Sie einen Vorgang zur Onlineindexneuerstellung für eine fortsetzbar ausgeführte Onlineindexneuerstellung fort. Legen Sie MAXDOP auf den Wert 2 und die Ausführungszeit für den fortsetzbar ausgeführten Index auf 240 Minuten fest. Sollte ein Index in der Sperre blockiert werden, warten Sie 10 Minuten, bevor Sie alle blockierenden Elemente entfernen.

      ALTER INDEX test_idx on test_table
         RESUME WITH (MAXDOP = 2, MAX_DURATION = 240 MINUTES,
         WAIT_AT_LOW_PRIORITY (MAX_DURATION = 10, ABORT_AFTER_WAIT = BLOCKERS)) ;
   

7. Brechen Sie einen fortsetzbaren Indexneuerstellungsvorgang ab, der ausgeführt wird oder angehalten wurde.

   ALTER INDEX test_idx on test_table ABORT ;
   

Weitere Informationen

• Leitfaden zur Architektur und zum Entwurf von SQL Server-Indizes
• Ausführen von Onlineindexvorgängen
• CREATE INDEX (Transact-SQL)
• CREATE SPATIAL INDEX (Transact-SQL)
• CREATE XML INDEX (Transact-SQL)
• DROP INDEX (Transact-SQL)
• Deaktivieren von Indizes und Einschränkungen
• XML-Indizes (SQL Server)
• Neuorganisieren und Neuerstellen von Indizes
• sys.dm_db_index_physical_stats (Transact-SQL)
• EVENTDATA (Transact-SQL)