ALTER INDEX (Transact-SQL)

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

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

    Spalten mit großen Objektdatentypen: image, text, ntext, varchar(max), nvarchar(max), varbinary(max) und xml.

    REBUILD PARTITION = partition_number

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

    REORGANIZE

    Indizes, bei denen ALLOW_PAGE_LOCKS auf OFF festgelegt ist

    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

    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
    Der Name der Datenbank.

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

  • table_or_view_name
    Der Name der Tabelle oder Sicht, die 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. Weitere Informationen finden Sie unter Auswählen eines Wiederherstellungsmodells für Indexvorgänge.

    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.

  • partition_number
    Die Nummer der Partition eines partitionierten Index, die 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 Database Engine (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.

  • REORGANIZE
    Gibt an, dass die Indexblattebene neu organisiert wird. Diese Klausel entspricht DBCC INDEXDEFRAG. 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. Zu den LOB-Datentypen gehören image, text, ntext, varchar(max), nvarchar(max), varbinary(max) und xml. Das Komprimieren dieser Daten kann die Speicherplatzverwendung verbessern. Die Standardeinstellung ist ON.

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

      Durch das Neuorganisieren eines gruppierten Index werden alle im gruppierten Index enthaltenen LOB-Spalten komprimiert. Durch das Neuorganisieren eines nicht gruppierten Index werden alle LOB-Spalten komprimiert, die im Index als (eingeschlossene) Nichtschlüsselspalten enthalten sind. Weitere Informationen finden Sie unter Erstellen von Indizes mit eingeschlossenen Spalten.

      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 LOB-Spalten nicht vorhanden sind.

  • SET ( <set_index option> [ ,... n] )
    Gibt Indexoptionen ohne das Neuerstellen oder Neuorganisieren des Index an. SET kann für einen deaktivierten Index nicht angegeben werden.

  • PAD_INDEX = { ON | OFF }
    Gibt die Indextextleerstellen an. Die Standardeinstellung ist OFF.

    • ON
      Der Prozentsatz des mit FILLFACTOR angegebenen freien Speicherplatzes wird für die Zwischenebenenseiten des Index 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 Database Engine (Datenbankmodul) die Blattebene jeder Indexseite während der Indexerstellung oder -änderung auffüllen soll. fillfactor muss ein ganzzahliger Wert von 1 bis 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. Database Engine (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 Database Engine (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 Index 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 ein größerer Speicherplatz während der Erstellung des Index verwendet.

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

    Wenn kein Sortierungsvorgang erforderlich ist oder wenn die Sortierung im Arbeitsspeicher ausgeführt werden kann, wird die SORT_IN_TEMPDB-Option ignoriert.

    Weitere Informationen finden Sie unter tempdb und Indexerstellung.

  • IGNORE_DUP_KEY = { ON | OFF }
    Gibt die Fehlermeldung an, wenn ein Einfügevorgang versucht, doppelte Schlüsselwerte in einen eindeutigen Index einzufügen. Die Option IGNORE_DUP_KEY gilt nur für Einfügevorgänge nach dem Erstellen oder Neuerstellen des Index. Beim Ausführen von CREATE INDEX, ALTER INDEX oder UPDATE hat die Option keine Auswirkungen. 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. Der Standardwert 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 die zugrunde liegenden Tabellen und zugeordneten Indizes für Abfragen und Datenänderungen während des Indexvorgangs verfügbar sind. 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

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

    • ON
      Langzeittabellensperren werden nicht für die Dauer des Indexvorgangs aufrechterhalten. Während der Hauptphase des Indexvorgangs wird nur eine beabsichtigte freigegebene Sperre (IS, Intent Shared) in der Quelltabelle aufrechterhalten. Auf diese Weise können Abfragen oder Aktualisierungen 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 auf einer lokalen temporären 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 Aktualisierungen der zugrunde liegenden Tabelle verhindert. Lesevorgänge, wie SELECT-Anweisungen, sind jedoch zulässig.

    Weitere Informationen finden Sie unter Funktionsweise von Onlineindexvorgängen. Weitere Informationen zu Sperren finden Sie unter Sperrmodi.

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

    • XML-Indizes

    • Indizes auf lokalen temporären Tabellen

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

    • Gruppierte Indizes, wenn die zugrunde liegende Tabelle LOB-Datentypen enthält

    • Nicht gruppierten Indizes, die mit LOG-Datentypenspalten definiert sind

    Nicht gruppierte Indizes können online neu erstellt werden, wenn die Tabelle LOB-Datentypen enthält, aber keine dieser Spalten in der Indexdefinition als Schlüssel- oder Nichtschlüsselspalten verwendet wird.

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

    • ON
      Zeilensperren sind beim Zugriff auf den Index zulässig. Database Engine (Datenbankmodul) legt fest, 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. Database Engine (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 max degree of parallelism (Option). Verwenden Sie MAXDOP, um die Anzahl der bei der Ausführung paralleler Pläne verwendeten Prozessoren einzuschränken. Der Höchstwert 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 die Generierung paralleler Pläne.

    • >1
      Schränkt die maximale Anzahl an Prozessoren auf die angegebene Anzahl ein, die in einem parallelen Indexvorgang verwendet wird.

    • 0 (Standardwert)
      Verwendet die tatsächliche Anzahl an Prozessoren oder weniger Prozessoren; dies hängt von der aktuellen Systemarbeitsauslastung ab.

    Weitere Informationen finden Sie unter Konfigurieren von Parallelindexvorgänge.

    HinweisHinweis

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

  • 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 Erstellen komprimierter Tabellen und Indizes.

  • 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. Beispiel:

    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änge.

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 Index 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 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 Database Engine (Datenbankmodul) die tatsächlichen aufgehobenen Seitenzuordnungen sowie deren zugeordnete Sperren, bis für die Transaktion ein Commit ausgeführt wird. Weitere Informationen finden Sie unter Löschen und Neuerstellen großer Objekte.

HinweisHinweis

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 Index möglicherweise nicht verringert wird. Weitere Informationen zu gemischten Blöcken finden Sie unter Grundlegendes zu Seiten und Blöcken.

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. In 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 Index 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 Index 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 Index 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 auch die Indexseiten komprimiert. Die Komprimierung basiert auf dem vorhandenen Füllfaktorwert. Verwenden Sie zum Anzeigen der Füllfaktoreinstellung sys.indexes.

Wenn ALL angegeben ist, werden sowohl gruppierte als auch nicht gruppierte relationale Indizes sowie 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. Beim Deaktivieren eines nicht gruppierten oder gruppierten Index in einer Sicht werden die Indexdaten physisch gelöscht. 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.

Wenn eine Tabelle in einer Transaktionsreplikationsveröffentlichung verwendet wird, können Sie Indizes, die Primärschlüsselspalten zugeordnet sind, nicht deaktivieren. Diese Indizes werden für die Replikation benötigt. Sie müssen zum Deaktivieren eines Index 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.

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 Seitensperroptionen

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. Database Engine (Datenbankmodul) wählt die entsprechende Sperre aus und kann diese von einer Zeilen- oder Seitensperre auf eine Tabellensperre ausweiten.

Bei Angabe von ALLOW_ROW_LOCKS = OFF und ALLOW_PAGE_LOCK = OFF sind beim Zugriff auf den Index nur Sperren auf Tabellenebene zulässig. Weitere Informationen zum Konfigurieren der Granularität von Sperren für einen Index finden Sie unter Anpassen der Sperren für einen Index.

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

Weitere Informationen finden Sie unter Sperrenausweitung (Datenbankmodul).

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 Onlineindexvorgänge, die zur gleichen Zeit durchgeführt werden, 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äumlioche 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).

Datenkomprimierung

Weitere Informationen zur Datenkomprimierung finden Sie unter Erstellen komprimierter Tabellen und Indizes.

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 auswirken wird.

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 aufweist.

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

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

Berechtigungen

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

Beispiele

A. Neuerstellen eines Index

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

USE AdventureWorks;
GO
ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee
REBUILD;
GO

B. Neuerstellen aller Indizes einer Tabelle und Angeben von Optionen

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

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

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

USE AdventureWorks;
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 AdventureWorks;
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 AdventureWorks;
GO
ALTER INDEX IX_Employee_ManagerID 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 auf der zugrunde liegenden Tabelle wird automatisch deaktiviert, und eine Warnmeldung wird angezeigt.

USE AdventureWorks;
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 AdventureWorks;
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. Dieses Beispiel setzt voraus, dass das Beispiel für einen partitionierten Index installiert wurde.

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. Ändern der Komprimierungseinstellung eines Indexes

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

Änderungsverlauf

Aktualisierter Inhalt

Entfernen Sie deaktivierte Indizes aus den Indizes, die mit REORGANIZE fehlschlagen.