ALTER INDEX (Transact-SQL)

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

Transact-SQL-Syntaxkonventionen

Syntax

ALTER INDEX { index_name | ALL }
    ON <object>
    { REBUILD 
        [ [ 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
}

<single_partition_rebuild_index_option> ::=
{
    SORT_IN_TEMPDB = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
}

<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 jedoch 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 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 oder deaktivierter Index
  REORGANIZE	Indizes, für die ALLOW_PAGE_LOCKS auf OFF festgelegt wurde
  REORGANIZE PARTITION = partition_number	Nicht partitionierter Index, XML-Index oder deaktivierter Index
  SET	Deaktivierter 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 Indexes 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 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.

  Hinweis:
  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 Indexes neu erstellt oder neu organisiert wird. PARTITION kann nicht angegeben werden, wenn index_name kein partitionierter Index ist.

• partition_number
  Die Nummer der Partition eines partitionierten Indexes, 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, sie können jedoch nicht auf eine Transact-SQL-Anweisung verweisen. partition_number muss vorhanden sein, andernfalls schlägt die Anweisung fehl.

• WITH (<single_partition_rebuild_index_option>)
  Die Optionen, die beim Neuerstellen einer einzelnen Partition angegeben werden können, sind SORT_IN_TEMPDB und MAXDOP (PARTITION = n). XML-Indizes können nicht in einem erneuten Erstellungsvorgang einer einzelnen Partition angegeben werden.

  Das Neuerstellen eines partitionierten Indexes 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 SQL Server 2005-Datenbankmodul. 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 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 Indexes werden alle im gruppierten Index enthaltenen LOB-Spalten komprimiert. Durch das Neuorganisieren eines nicht gruppierten Indexes 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. 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 Indexes an. SET kann für einen deaktivierten Index nicht angegeben werden.

• PAD_INDEX = { ON | OFF }
  Gibt den Indextextabstand an. Die Standardeinstellung ist OFF.

  • ON
    Der Prozentsatz des mit FILLFACTOR angegebenen freien Speicherplatzes wird auf die Zwischenebenenseiten des Indexes angewendet. Wenn FILLFACTOR nicht angegeben ist und PAD_INDEX auf ON festgelegt ist, 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 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.

  Hinweis:
  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 Indexes. 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.

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

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

  • ON
    Die zum Erstellen des Indexes verwendeten Zwischenergebnisse der Sortierung werden in tempdb gespeichert. Wenn tempdb sich auf einem anderen Datenträgersatz befindet als die Benutzerdatenbank, kann dies die zum Erstellen eines Indexes erforderliche Zeit reduzieren. Allerdings wird ein größerer Speicherplatz während der Erstellung des Indexes erforderlich.
  • 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 Fehlerantwort für doppelte Schlüsselwerte in einer Einfügetransaktion mehrerer Zeilen in einem eindeutigen gruppierten oder eindeutigen nicht gruppierten Index an. Die Standardeinstellung ist OFF.

  • ON
    Eine Warnmeldung wird ausgegeben; und nur die Zeilen, die gegen den UNIQUE-Index verstoßen, erzeugen einen Fehler.
  • OFF
    Eine Fehlermeldung wird ausgegeben, und für die gesamte Transaktion wird ein Rollback ausgeführt.

  Die IGNORE_DUP_KEY-Einstellung gilt nur für Einfügevorgänge, die nach dem Erstellen oder Neuerstellen des Indexes auftreten. Die Einstellung hat während des Indexvorgangs keine Auswirkungen. IGNORE_DUP_KEY hat in einer UPDATE-Anweisung keine Auswirkungen.

  IGNORE_DUP_KEY kann für XML-Indizes und für Indizes, die in einer Sicht erstellt werden, nicht auf ON festgelegt werden. Weitere Informationen finden Sie unter CREATE INDEX (Transact-SQL).

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

  Wichtig:
  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.

  Hinweis:
  Onlineindexvorgänge sind nur in SQL Server 2005 Enterprise Edition verfügbar.

  • ON
    Langzeittabellensperren werden nicht für die Dauer des Indexvorgangs aufrechterhalten. Während der Hauptphase des Indexvorgangs wird nur eine beabsichtigte gemeinsame 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 gemeinsamen 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 (Schema Modification – 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
    Für die Dauer des Indexvorgangs werden Tabellensperren angewendet. Ein Offlineindexvorgang, bei dem ein gruppierter Index erstellt, neu erstellt oder gelöscht wird bzw. ein nicht gruppierter Index neu erstellt oder gelöscht wird, richtet eine Schemaänderungssperre (Sch-M) für die Tabelle ein. Dies verhindert sämtliche Benutzerzugriffe auf die zugrunde liegende Tabelle für die Dauer des Vorgangs. Ein Offlineindexvorgang, bei dem ein nicht gruppierter Index erstellt wird, richtet eine gemeinsame Sperre (S) für die Tabelle ein. Dies verhindert Aktualisierungen der zugrunde liegenden Tabelle, lässt jedoch Lesevorgänge, wie z. B. SELECT-Anweisungen, zu.

  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 online neu erstellt werden, mit Ausnahme von:

  • deaktivierten Indizes
  • XML-Indizes
  • Indizes für lokale temporäre Tabellen
  • partitionierten Indizes
  • gruppierten Indizes, wenn die zugrunde liegende Tabelle LOB-Datentypen enthält
  • nicht gruppierten Indizes, die mit LOG-Datentypspalten definiert sind

  Nicht gruppierte Indizes können online neu erstellt werden, wenn die Tabelle LOB-Datentypen enthält, jedoch 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. Die Standardeinstellung ist ON.

  • ON
    Zeilensperren sind beim Zugreifen auf den Index zulässig. In Datenbankmodul ist festgelegt, 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 legt fest, wann Seitensperren verwendet werden.
  • OFF
    Seitensperren werden nicht verwendet.

  Hinweis:
  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.

  Mögliche Werte für max_degree_of_parallelism sind:

  • 1
    Unterdrückt das Generieren 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 je nach der aktuellen Arbeitsauslastung des Systems maximal die tatsächliche Anzahl von Prozessoren.

  Weitere Informationen finden Sie unter Konfigurieren von Parallelindexvorgänge.

  Hinweis:
  Parallelindexvorgänge sind nur in SQL Server 2005 Enterprise Edition verfügbar.

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.

Hinweis:
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, die im Zusammenhang zur Bearbeitung des Indexes stehen. 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 sich mindestens ein Index in einer Offline- oder in einer schreibgeschützten Dateigruppe befindet, erzeugt die Anweisung einen Fehler.

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 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. Weitere Informationen finden Sie unter Löschen und Neuerstellen großer Objekte.

Hinweis:

Durch erneutes Erstellen oder Reorganisieren von kleinen Indizes wird die Fragmentierung oft nicht verringert. 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. Weitere Informationen zu gemischten Erweiterungen finden Sie unter Seiten und Blöcke.

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 2005 können Sie möglicherweise weiterhin solche Inkonsistenzen zwischen dem Index und dem gruppierten Index reparieren, indem Sie die Neuerstellung eines nicht gruppierten Indexes 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 Indexes (oder Heaps) erzwungen, und Inkonsistenzen werden somit entfernt. Wie in früheren Versionen wird zum Entfernen von Inkonsistenzen auch in dieser Version empfohlen, 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 physikalisch 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 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 Indexes wird Benutzern der Zugriff auf den Index oder, im Fall von gruppierten Indizes, auf die zugrunde liegenden Daten verwehrt. Die Indexdefinition bleibt im Systemkatalog erhalten. Beim Deaktivieren eines nicht gruppierten oder gruppierten Indexes in einer Sicht werden die Indexdaten physikalisch gelöscht. Durch das Deaktivieren eines gruppierten Indexes 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 Indexes anzuzeigen.

Wenn eine Tabelle in einer Transaktionsreplikationspublikation verwendet wird, können Sie Indizes, die Primärschlüsselspalten zugeordnet sind, nicht deaktivieren. Diese Indizes werden von der Replikation benötigt. Sie müssen zum Deaktivieren eines Indexes zuerst die Tabelle aus der Publikation 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 Indexes 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 Indexes 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. 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 gemeinsame Sperren (S – Shared), Aktualisierungssperren (U – Update) und exklusive Sperren (X – Exclusive) für die Seite unzulässig. Datenbankmodul kann weiterhin eine beabsichtigte gemeinsame Seitensperre des Typs IS (Intent Shared), IU (Intent Update) oder IX (Intent Exclusive) für interne Zwecke einrichten.

Weitere Informationen finden Sie unter Sperrenausweitung (Datenbankmodul).

Onlineindexvorgänge

Wenn Sie einen Index neu erstellen und die ONLINE-Option auf ON festgelegt ist, 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 Indexes wird stets online durchgeführt. Während des Prozesses werden Sperren nicht dauerhaft aufrechterhalten; daher werden ausgeführte Abfragen oder Updates 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 Indexes keinen neuen Index auf derselben Tabelle erstellen.

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

Berechtigungen

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

Beispiele

A. Neuerstellen eines Indexes

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

Das folgende Beispiel gibt das ALL-Schlüsselwort an. Auf diese Weise werden alle Indizes neu erstellt, die der Tabelle zugeordnet sind. 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 Indexes 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 WITH (LOB_COMPACTION)-Option nicht erforderlich ist, da der Standardwert 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 Indexes

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 Indexes

Im folgenden Beispiel wird eine einzelne Partition mit der Partitionsnummer 5 des partitionierten IX_TransactionHistory_TransactionDate-Indexes neu erstellt. In diesem Beispiel wird vorausgesetzt, dass die Stichprobe des partitionierten Indexes installiert wurde. Weitere Informationen zur Installation finden Sie unter Readme_PartitioningScript.

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

Siehe auch

Verweis

CREATE INDEX (Transact-SQL)
sys.dm_db_index_physical_stats
EVENTDATA (Transact-SQL)

Andere Ressourcen

Deaktivieren von Indizes
Indizes für Spalten vom xml-Datentyp
Ausführen von Onlineindexvorgängen
Neuorganisieren und Neuerstellen von Indizes

Hilfe und Informationen

Informationsquellen für SQL Server 2005