ALTER INDEX (Transact-SQL)

  • Artikel

Ändert eine vorhandene Tabelle oder einen vorhandenen Sichtindex (relational oder XML), indem der Index deaktiviert, neu erstellt oder neu organisiert bzw. durch Einstellungsoptionen auf dem Index geändert wird.

Themenlink (Symbol) Transact-SQL-Syntaxkonventionen

Syntax

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 ( LOB_COMPACTION = { ON | OFF } ) ]
  | SET ( <set_index_option> [ ,...n ] ) 
    }
[ ; ]

<object> ::=
{
    [ database_name. [ schema_name ] . | schema_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 }
  | ONLINE = { ON | OFF } 
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE } 
     [ ON PARTITIONS ( { <partition_number_expression> | <range> } 
     [ , ...n ] ) ]
}
<range> ::= 
<partition_number_expression> TO <partition_number_expression>
}

<single_partition_rebuild_index_option> ::=
{
    SORT_IN_TEMPDB = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE } }
}

<set_index_option>::=
{
    ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
}

Argumente

  • index_name
    Der Name des Indexes. 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-Klauseln verursacht bei der Anweisung einen Fehler, wenn mindestens ein Index in einer Offline- oder schreibgeschützten Dateigruppe vorhanden 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.

    Angeben von ALL mit diesem Vorgang

    Erzeugt einen Fehler, wenn mindestens einer dieser Indextypen in der Tabelle vorhanden ist

    REBUILD WITH ONLINE = ON

    XML-Index

    Räumlicher Index

    REBUILD PARTITION = partition_number

    Nicht partitionierter Index, XML-Index, räumlicher Index oder deaktivierter Index

    REORGANIZE

    Indizes mit ALLOW_PAGE_LOCKS wurden auf OFF festgelegt

    REORGANIZE PARTITION = partition_number

    Nicht partitionierter Index, XML-Index, räumlicher Index oder deaktivierter Index

    IGNORE_DUP_KEY = ON

    Räumlicher Index

    XML-Index

    ONLINE = ON

    Räumlicher Index

    XML-Index
    VorsichtshinweisVorsicht

    Ausführlichere Informationen zu Indexvorgängen, die online ausgeführt werden können, finden Sie unter Richtlinien für Onlineindexvorgänge.

    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 Partitionierte Tabellen und Indizes.

  • database_name
    Ist der Name der Datenbank.

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

  • table_or_view_name
    Der Name der Tabelle oder Sicht, die dem Index zugeordnet ist. Verwenden Sie zum Anzeigen eines Berichts der Indizes für ein Objekt die sys.indexes-Katalogsicht.

  • REBUILD [ WITH (<rebuild_index_option> [ ,... n]) ]
    Gibt an, dass der Index mit denselben Spalten, demselben Indextyp, derselben Attributeindeutigkeit und Sortierreihenfolge neu erstellt wird. Diese Klausel entspricht DBCC DBREINDEX. Mit REBUILD wird ein deaktivierter Index aktiviert. Durch das Neuerstellen eines gruppierten Index erfolgt nicht die Neuerstellung von zugeordneten nicht gruppierten Indizes, es sei denn, das Schlüsselwort ALL ist angegeben. Wenn Indexoptionen nicht angegeben sind, werden die vorhandenen Indexoptionen angewendet, die in sys.indexes gespeichert sind. Für alle Indexoptionen, deren Werte nicht in sys.indexes gespeichert sind, wird der Standard angewendet, der in der Argumentdefinition der Option angegeben ist.

    Die Optionen ONLINE und IGNORE_DUP_KEY sind nicht gültig, wenn Sie einen XML-Index oder einen räumlichen Index neu erstellen.

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

    Der Vorgang der Neuerstellung kann minimal protokolliert werden, wenn die Datenbankwiederherstellung entweder auf das Modell der massenprotokollierten oder der einfachen Wiederherstellung festgelegt ist.

    HinweisHinweis

    Wenn Sie einen primären XML-Index neu erstellen, ist die zugrunde liegende Benutzertabelle während des Indexvorgangs nicht verfügbar.

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

    PARTITION = ALL erstellt alle Partitionen neu.

    VorsichtshinweisVorsicht

    Das Erstellen bzw. Neuerstellen von nicht ausgerichteten Indizes für eine Tabelle mit mehr als 1.000 Partitionen ist möglich, wird aber nicht unterstützt. Dies hätte Leistungseinbußen oder eine zu hohe Speicherauslastung während der Vorgänge zur Folge. Es empfiehlt sich, bei mehr als 1.000 Partitionen nur ausgerichtete Indizes zu verwenden.

  • partition_number
    Die Partitionsnummer eines partitionierten Indexes, der neu erstellt oder neu organisiert werden soll. partition_number ist ein konstanter Ausdruck, der auf Variablen verweisen kann. Bei diesen Variablen kann es sich um Funktionen und benutzerdefinierte Variablen oder 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>)
    Die Optionen, die für das Neuerstellen einer einzelnen Partition angegeben werden können, sind SORT_IN_TEMPDB, MAXDOP und DATA_COMPRESSION (PARTITION = n). XML-Indizes können nicht in einem erneuten Erstellungsvorgang einer einzelnen Partition angegeben werden.

    Das Neuerstellen eines partitionierten Index kann nicht online ausgeführt werden. Die gesamte Tabelle ist während dieses Vorgangs gesperrt.

  • DISABLE
    Markiert den Index als deaktiviert und als nicht verfügbar für die Verwendung in Datenbankmodul. Jeder Index kann deaktiviert werden. Die Indexdefinition eines deaktivierten Index bleibt weiterhin im Systemkatalog ohne zugrunde liegende Indexdaten bestehen. Durch das Deaktivieren eines gruppierten Index wird Benutzern der Zugriff auf die zugrunde liegenden Tabellendaten verwehrt. 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
    Gibt an, dass die Indexblattebene neu organisiert wird. Die ALTER INDEX REORGANIZE-Anweisung wird immer online ausgeführt. Das bedeutet, dass blockierende Langzeitsperren für Tabellen nicht aufrechterhalten werden und Abfragen oder Updates der zugrunde liegenden Tabelle während der ALTER INDEX REORGANIZE-Transaktion weiterhin bestehen können. REORGANIZE kann für einen deaktivierten Index bzw. für einen Index, bei dem ALLOW_PAGE_LOCKS auf OFF festgelegt ist, nicht angegeben werden.

  • WITH ( LOB_COMPACTION = { ON | OFF } )
    Gibt an, dass alle Seiten, die LOB-Daten enthalten, komprimiert werden. Die LOB-Datentypen sind image, text, ntext, varchar(max), nvarchar(max), varbinary(max) und xml. Durch das Komprimieren dieser Daten kann eine bessere Verwendung des Speicherplatzes erzielt werden. Die Standardeinstellung ist ON.

    • ON
      Alle Seiten, die LOB-Daten beinhalten, werden komprimiert.

      Durch das Neuorganisieren eines angegebenen gruppierten Indexes werden alle LOB-Spalten komprimiert, die in dem gruppierten Index enthalten sind. Durch das Neuorganisieren eines nicht gruppierten Indexes werden alle LOB-Spalten komprimiert, die (eingeschlossene) Nichtschlüsselspalten im Index sind.

      Wenn ALL angegeben ist, werden alle der angegebenen Tabelle oder Sicht zugeordneten Indizes neu organisiert; und alle LOB-Spalten, die dem gruppierten Index, der zugrunde liegenden Tabelle oder dem nicht gruppierten Index mit eingeschlossenen Spalten zugeordnet sind, werden komprimiert.

    • OFF
      Seiten, die LOB-Daten enthalten, werden nicht komprimiert.

      Die Einstellung OFF hat keine Auswirkungen auf einen Heap.

    Die LOB_COMPACTION-Klausel wird ignoriert, wenn keine LOB-Spalten vorhanden sind.

  • 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. Die Standardeinstellung ist OFF.

    • ON
      Der Prozentsatz des mit FILLFACTOR angegebenen freien Speicherplatzes wird für die Zwischenebenenseiten des Indexes angewendet. Wenn FILLFACTOR nicht angegeben ist, und PAD_INDEX ist auf ON festgelegt, 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 Index 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 Datenbankmodul die Blattebene jeder Indexseite während der Indexerstellung oder -änderung auffüllen soll. fillfactor muss ein ganzzahliger Wert zwischen 1 und 100 sein. Der Standardwert ist 0.

    HinweisHinweis

    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. Datenbankmodul hält den angegebenen Prozentsatz des Speicherplatzes nicht dynamisch auf den Seiten frei. Weitere Informationen finden Sie unter CREATE INDEX (Transact-SQL).

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

    Wichtiger HinweisWichtig

    Das Erstellen oder Ändern eines gruppierten Index mit einem FILLFACTOR-Wert wirkt sich auf den Speicherplatz aus, den die Daten belegen, da Datenbankmodul die Daten beim Erstellen des gruppierten Index neu verteilt.

  • SORT_IN_TEMPDB = { ON | OFF }
    Gibt an, ob die Sortierungsergebnisse in tempdb gespeichert werden sollen. Die Standardeinstellung ist OFF.

    • ON
      Die Zwischenergebnisse der Sortierung, die zum Erstellen des Indexes verwendet werden, werden in tempdb gespeichert. Wenn tempdb sich auf einem anderen Datenträgersatz befindet als die Benutzerdatenbank, kann dies die zum Erstellen eines Index erforderliche Zeit reduzieren. Allerdings wird dadurch die Menge an Speicherplatz erhöht, die während der Indexerstellung verwendet wird.

    • OFF
      Die Zwischenergebnisse des Sortierens werden in derselben Datenbank wie der Index gespeichert.

    Wenn keine Sortiervorgänge erforderlich ist oder die Sortierung im Arbeitsspeicher erfolgen kann, wird die Option SORT_IN_TEMPDB 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 Indexes. Die Standardeinstellung ist OFF.

    • ON
      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 erstellt werden, nicht eindeutige Indizes, XML-Indizes, räumliche und gefilterte Indizes nicht auf ON festgelegt werden.

    Um IGNORE_DUP_KEY anzuzeigen, verwenden Sie sys.indexes.

    In abwärtskompatibler Syntax ist WITH IGNORE_DUP_KEY gleichwertig mit WITH IGNORE_DUP_KEY = ON.

  • STATISTICS_NORECOMPUTE = { ON | OFF }
    Gibt an, ob Verteilungsstatistiken neu berechnet werden. Die Standardeinstellung ist OFF.

    • ON
      Veraltete Statistiken werden nicht automatisch neu berechnet.

    • OFF
      Das automatische Aktualisieren von Statistiken ist aktiviert.

    Um das automatische Aktualisieren von Statistiken wiederherzustellen, müssen Sie STATISTICS_NORECOMPUTE auf OFF festlegen oder die UPDATE STATISTICS-Anweisung ohne die NORECOMPUTE-Klausel ausführen.

    Wichtiger HinweisWichtig

    Wenn Sie die automatische Neuberechnung von Verteilungsstatistiken deaktivieren, wählt der Abfrageoptimierer möglicherweise nicht die optimalen Ausführungspläne für Abfragen aus, an denen die Tabelle beteiligt ist.

  • ONLINE = { ON | OFF }
    Gibt an, ob zugrunde liegende Tabellen oder zugehörige Indizes für Abfragen und Datenänderungen während des Indexvorgangs zur Verfügung stehen. Die Standardeinstellung 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.

    HinweisHinweis

    Onlineindexvorgänge sind nicht in jeder Edition von Microsoft SQL Server verfügbar. Eine Liste der Funktionen, die von den Editionen von SQL Server unterstützt werden, finden Sie unter Von den SQL Server 2012-Editionen unterstützte Funktionen.

    • ON
      Lang andauernde Sperren werden nicht für die Dauer des Indexvorgangs aufrechterhalten. Während der Hauptphase des Indexvorgangs wird nur eine beabsichtigte gemeinsame (Intent Share, IS) 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 freigegebenen Sperre (S) belegt. Am Ende des Vorgangs wird für kurze Zeit eine Sperre (S) für die Quelle eingerichtet, wenn ein nicht gruppierter Index erstellt wird. Eine Schemaänderungssperre (SCH-M) wird eingerichtet, 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
      Die Tabellensperren werden für die Dauer des Indexvorgangs angewendet. Ein Offlineindexvorgang, der einen gruppierten Index erstellt, neu erstellt oder löscht oder einen nicht gruppierten Index, einen räumlichen Index oder einen XML-Index löscht, aktiviert eine Schemaänderungssperre (Sch-M) für die Tabelle. Dadurch wird verhindert, dass Benutzer für die Dauer des Vorgangs auf die zugrunde liegende Tabelle zugreifen können. Ein Offlineindexvorgang, bei dem ein nicht gruppierter Index erstellt wird, aktiviert eine gemeinsame 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 Funktionsweise von Onlineindexvorgängen.

    Indizes, einschließlich Indizes globaler temporärer Tabellen, können online neu erstellt werden, mit Ausnahme von:

    • XML-Indizes

    • Indizes auf lokalen temporären Tabellen

    • Eine Teilmenge eines partitionierten Indexes (ein ganzer partitionierter Index kann online neu erstellt werden).

  • ALLOW_ROW_LOCKS = { ON | OFF }
    Gibt an, ob Zeilensperren zulässig sind. Die Standardeinstellung ist ON.

    • ON
      Zeilensperren sind beim Zugriff auf den Index zulässig. Datenbankmodul bestimmt, wann Zeilensperren verwendet werden.

    • OFF
      Es werden keine Zeilensperren verwendet.

  • ALLOW_PAGE_LOCKS = { ON | OFF }
    Gibt an, ob Seitensperren zulässig sind. Die Standardeinstellung ist ON.

    • ON
      Seitensperren sind beim Zugriff auf den Index zulässig. Datenbankmodul bestimmt, wann Seitensperren verwendet werden.

    • OFF
      Seitensperren werden nicht verwendet.

    HinweisHinweis

    Ein Index kann nicht neu organisiert werden, wenn ALLOW_PAGE_LOCKS auf OFF festgelegt ist.

  • MAXDOP **=**max_degree_of_parallelism
    Überschreibt die Konfigurationsoption Max. Grad an Parallelität für die Dauer des Indexvorgangs. Weitere Informationen finden Sie unter Konfigurieren der Serverkonfigurationsoption Max. Grad an Parallelität. Verwenden Sie MAXDOP, um die Anzahl von Prozessoren zu begrenzen, die bei der Ausführung paralleler Pläne verwendet werden. Die maximal zulässige Anzahl ist 64 Prozessoren.

    Wichtiger HinweisWichtig

    Obwohl die MAXDOP-Option syntaktisch für alle XML-Indizes unterstützt wird, verwendet ALTER INDEX gegenwärtig für einen räumlichen Index oder einen primären XML-Index nur einen einzelnen Prozessor.

    Mögliche Werte für max_degree_of_parallelism sind:

    • 1
      Unterdrückt das Generieren paralleler Pläne.

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

    • 0 (Standardwert)
      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.

    HinweisHinweis

    Parallele Indexvorgänge sind nicht in jeder Edition von Microsoft SQL Server verfügbar. Eine Liste der Funktionen, die von den Editionen von SQL Server unterstützt werden, finden Sie unter Von den SQL Server 2012-Editionen unterstützte Funktionen.

  • DATA_COMPRESSION
    Gibt die Datenkomprimierungsoption für den angegebenen Index, die Partitionsnummer oder den Bereich von Partitionen an. Folgende Optionen stehen zur Verfügung:

    • NONE
      Der Index oder die angegebenen Partitionen werden nicht komprimiert.

    • ROW
      Der Index oder die angegebenen Partitionen werden mit Zeilenkomprimierung komprimiert.

    • PAGE
      Der Index oder die angegebenen Partitionen werden mit Seitenkomprimierung komprimiert.

    Weitere Informationen zur Komprimierung finden Sie unter Datenkomprimierung.

  • ON PARTITIONS ( { <partition_number_expression> | <range> } [,...n] )
    Gibt die Partitionen an, für die die DATA_COMPRESSION-Einstellung gilt. 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-Option für alle Partitionen eines partitionierten Index.

    <partition_number_expression> kann auf die folgenden Weisen angegeben werden:

    • Geben Sie die Nummer der Partition an, beispielsweise: ON PARTITIONS (2).

    • Geben Sie die Partitionsnummern mehrerer einzelner Partitionen durch Trennzeichen 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, beispielsweise: 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)
)

Hinweise

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

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

HinweisHinweis

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 Multiprozessorcomputern werden für ALTER INDEX REBUILD automatisch mehr Prozessoren verwendet, um Scan- und Sortierungsvorgänge auszuführen. Dies geschieht in gleicher Weise wie für andere Abfragen. Wenn Sie ALTER INDEX REORGANIZE mit oder ohne LOB_COMPACTION ausführen, ist der Wert der Konfigurationsoption Max. Grad an Parallelität ein einzelner Threadvorgang. Weitere Informationen finden Sie unter Konfigurieren von Parallelindexvorgängen.

Ein Index kann nicht neu organisiert oder neu erstellt werden, wenn es sich bei der Dateigruppe, in der er sich befindet, um eine Offline- oder eine schreibgeschützte Dateigruppe handelt. Wenn das Schlüsselwort ALL angegeben ist, und mindestens ein Index befindet sich in einer Offline- oder in einer schreibgeschützten Dateigruppe, erzeugt die Anweisung einen Fehler.

Neuerstellen von Indizes

Beim Neuerstellen eines Indexes wird der Index gelöscht und anschließend 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 aufeinander folgende Seiten geordnet. Wenn ALL angegeben ist, werden alle Indizes der Tabelle gelöscht und in einer einzelnen Transaktion neu erstellt. FOREIGN KEY-Einschränkungen müssen nicht im Voraus gelöscht werden. Wenn Indizes mit mindestens 128 Blöcken neu erstellt werden, verzögert Datenbankmodul die tatsächlichen aufgehobenen Seitenzuordnungen sowie deren zugeordnete Sperren, bis für die Transaktion ein Commit ausgeführt wird.

Das erneute Erstellen oder Reorganisieren von kleinen Indizes verringert oft nicht die Fragmentierung. Die Seiten kleiner Indizes werden in gemischten Blöcken gespeichert. Gemischte Blöcke sind für bis zu acht Objekte freigegeben, sodass die Fragmentierung in einem kleinen Index durch die Reorganisation oder das erneute Erstellen des Indexes möglicherweise nicht verringert wird.

In SQL Server 2012 werden Statistiken nicht durch das Scannen aller Zeilen in der Tabelle erstellt, wenn ein partitionierter Index erstellt oder neu erstellt wird. Der Abfrageoptimierer generiert stattdessen Statistiken mithilfe des Standardalgorithmus zur Stichprobenentnahme. Um Statistiken zu partitionierten Indizes durch das Scannen aller Zeilen in der Tabelle abzurufen, verwenden Sie CREATE STATISTICS oder UPDATE STATISTICS mit der FULLSCAN-Klausel.

In früheren Versionen von SQL Server konnte in einigen Fällen ein nicht gruppierter Index neu erstellt werden, um durch Hardwarefehler verursachte Inkonsistenzen zu korrigieren. Ab SQL Server 2008 können Sie weiterhin solche Inkonsistenzen zwischen dem Index und dem gruppierten Index reparieren, indem Sie die Neuerstellung eines nicht gruppierten Index offline durchführen. Sie können die Inkonsistenzen eines nicht gruppierten Indexes jedoch nicht reparieren, indem Sie den Index online neu erstellen, da der Online-Neuerstellungsmechanismus den vorhandenen nicht gruppierten Index als Grundlage für die Neuerstellung verwendet und somit die Inkonsistenzen bestehen bleiben. Wird der Index hingegen offline neu erstellt, wird ein Scan des gruppierten Index (oder Heaps) erzwungen, und Inkonsistenzen werden somit entfernt. Wie in früheren Versionen wird zum Entfernen von Inkonsistenzen auch in dieser Version empfohlenen, die betroffenen Daten aus einer Sicherung wiederherzustellen. Die Inkonsistenzen des Indexes können möglicherweise auch repariert werden, indem der nicht gruppierte Index offline neu erstellt wird. Weitere Informationen finden Sie unter DBCC CHECKDB (Transact-SQL).

Neuorganisieren von Indizes

Beim Neuorganisieren eines Indexes werden minimale Systemressourcen verwendet. Dabei wird die Blattebene von gruppierten und nicht gruppierten Indizes in Tabellen und Sichten defragmentiert, indem die Blattebenenseiten physisch neu geordnet werden, um mit der logischen Reihenfolge der Blattknoten von links nach rechts übereinzustimmen. Durch das Neuorganisieren werden die Indexseiten außerdem komprimiert. Die Komprimierung basiert auf dem vorhandenen Füllfaktorwert. Verwenden Sie zum Anzeigen der Füllfaktoreinstellung sys.indexes.

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; diese finden Sie in der ALL-Definition im Abschnitt Argumente.

Weitere Informationen finden Sie unter Neuorganisieren und Neuerstellen von Indizes.

Deaktivieren von Indizes

Durch das Deaktivieren eines Index wird Benutzern der Zugriff auf den Index sowie auf die zugrunde liegenden Daten von gruppierten Indizes verwehrt. Die Indexdefinition bleibt im Systemkatalog erhalten. Das Deaktivieren von gruppierten und nicht gruppierten Indizes für einer Sicht löscht die physischen Indexdaten. Durch das Deaktivieren eines gruppierten Index wird Benutzern der Zugriff auf die Daten verwehrt; 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.

Festlegen von Optionen

Sie können die Optionen ALLOW_ROW_LOCKS, ALLOW_PAGE_LOCKS, IGNORE_DUP_KEY und STATISTICS_NORECOMPUTE für einen angegebenen Index festlegen, ohne die Neuerstellung oder das Neuorganisieren dieses Index durchzuführen. Die geänderten Werte werden sofort auf den Index angewendet. Verwenden Sie sys.indexes, um diese Einstellungen anzuzeigen. Weitere Informationen finden Sie unter Festlegen von Indexoptionen.

Zeilen- und Seitensperren (Optionen)

Wenn ALLOW_ROW_LOCKS auf ON und ALLOW_PAGE_LOCK auf ON festgelegt ist, sind Sperren auf Zeilen-, Seiten- und Tabellenebene beim Zugriff auf den Index zulässig. Das Datenbankmodul wählt die geeignete Sperre aus und kann die Sperre von einer Zeilen- oder Seitensperre auf eine Tabellensperre ausweiten.

Wenn ALLOW_ROW_LOCKS auf OFF und ALLOW_PAGE_LOCK auf OFF festgelegt sind, sind beim Zugriff auf den Index nur Sperren auf Tabellenebene zulässig.

Wenn beim Festlegen der Zeilen- oder Seitensperroptionen 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:

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 freigegebene Sperren (S, Shared), Updatesperren (U, Update) und exklusive Sperren (X, Exclusive) für die Seite unzulässig. Datenbankmodul kann weiterhin eine beabsichtigte Seitensperre (IS, IU oder IX) für interne Zwecke abrufen.

Onlineindexvorgä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. Exklusive Tabellensperren werden während des Änderungsprozesses nur für einen kurzen Zeitraum aufrechterhalten.

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

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

  • Erstellen mehrerer nicht gruppierter Indizes.

  • Neuorganisieren unterschiedlicher Indizes auf 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 auf derselben Tabelle neu erstellen bzw. beim Neuerstellen eines vorhandenen Index keinen neuen Index auf derselben Tabelle erstellen.

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

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, weil der räumliche Index eine Schemasperre eingerichtet hat.

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.

Zum Ändern von Optionen, die einem bestimmten räumlichen Index eigen sind, beispielsweise BOUNDING_BOX oder GRID, können Sie entweder eine CREATE SPATIAL INDEX-Anweisung mit der Angabe DROP_EXISTING = ON verwenden, oder Sie löschen den räumlichen Index und erstellen einen neuen räumlichen Index. Ein Beispiel hierzu finden Sie unter CREATE SPATIAL INDEX (Transact-SQL).

Einschränkungen für Columnstore-Indizes

Außer für die REBUILD-Option kann ein für den xVelocity-Arbeitsspeicher optimierter ColumnStore-Index nicht geändert werden. Stattdessen sollte der columnstore-Index gelöscht und neu erstellt werden.

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 des Komprimierungsstatus auf eine Tabelle, einen Index oder eine Partition auswirkt.

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

  • Bei Verwendung von ALTER INDEX ALL ..., können Sie die Komprimierungseinstellung einer einzelnen Partition nicht ändern, wenn die Tabelle blockfreie Indizes hat.

  • Mit der ALTER INDEX <Index>... REBUILD PARTITION ... -Syntax wird die angegebene Partition des Indexes neu erstellt.

  • Mit der ALTER INDEX <Index>... REBUILD WITH ... -Syntax werden alle Partitionen des Indexes neu erstellt.

Statistik

Wenn Sie ALTER INDEX ALL … in einer Tabelle ausführen, werden nur die Statistikmitarbeiter mit Indizes aktualisiert. Automatische oder manuelle Statistiken, die statt eines Indexes in der Tabelle erstellt wurden, werden nicht aktualisiert.

Berechtigungen

Zum Ausführen von ALTER INDEX benötigen Sie mindestens die ALTER-Berechtigung auf der Tabelle bzw. Sicht.

Beispiele

A.Neuerstellen eines Indexes

Im folgenden Beispiel wird ein einzelner Index auf der Employee-Tabelle neu erstellt.

USE AdventureWorks2012;
GO
ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD;
GO

B.Neuerstellen aller Indizes für eine Tabelle und Angeben von Optionen

Im folgenden Beispiel wird das Schlüsselwort ALL angegeben. Hier werden alle der Tabelle zugeordneten Indizes neu erstellt. Drei Optionen werden angegeben.

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

C.Neuorganisieren eines Index mit LOB-Komprimierung

Das folgende Beispiel organisiert einen einzelnen gruppierten Index neu. Da der Index einen LOB-Datentyp auf Blattebene enthält, komprimiert die Anweisung außerdem alle Seiten, die die Large Object-Daten enthalten. Beachten Sie, dass die Angabe der WITH (LOB_COMPACTION)-Option nicht erforderlich ist, da der Standardwert auf ON festgelegt ist.

USE AdventureWorks2012;
GO
ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto
REORGANIZE ;
GO

D.Festlegen von Optionen für einen Index

Im folgenden Beispiel werden mehrere Optionen auf dem AK_SalesOrderHeader_SalesOrderNumber-Index festgelegt.

USE AdventureWorks2012;
GO
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 auf der Employee-Tabelle deaktiviert.

USE AdventureWorks2012;
GO
ALTER INDEX IX_Employee_OrganizationNode ON HumanResources.Employee
DISABLE ;
GO

F.Deaktivieren von Einschränkungen

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

USE AdventureWorks2012;
GO
ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department
DISABLE ;
GO

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.

USE AdventureWorks2012;
GO
ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department
REBUILD ;
GO

Die FOREIGN KEY-Einschränkung ist dann 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-Index neu erstellt.

USE AdventureWorks;
GO
-- 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;
GO

I.IÄndern der Komprimierungseinstellung eines Index

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

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

Weitere Beispiele zur Datenkomprimierung finden Sie unter Datenkomprimierung.

Siehe auch

Verweis

CREATE INDEX (Transact-SQL)

CREATE SPATIAL INDEX (Transact-SQL)

CREATE XML INDEX (Transact-SQL)

DROP INDEX (Transact-SQL)

sys.dm_db_index_physical_stats (Transact-SQL)

EVENTDATA (Transact-SQL)

Konzepte

Deaktivieren von Indizes und Einschränkungen

XML-Indizes (SQL Server)

Ausführen von Onlineindexvorgängen

Neuorganisieren und Neuerstellen von Indizes