ALTER TABLE index_option (Transact-SQL)

  • Artikel

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL Managed Instance

Gibt eine Gruppe von Optionen an, die auf einen Index angewendet werden können, der Teil einer mit ALTER TABLE erstellten Einschränkungsdefinition ist.

Eine vollständige Beschreibung der Indexoptionen finden Sie unter CREATE INDEX.

Transact-SQL-Syntaxkonventionen

Syntax

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

<range> ::=
<partition_number_expression> TO <partition_number_expression>

<single_partition_rebuild__option> ::=
{
    SORT_IN_TEMPDB = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE } }
  | ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF }
}

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

Argumente

PAD_INDEX = { ON | OFF }

Gilt für: SQL Server 2008 (10.0.x) und höhere Versionen

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

  • ON

    Der Prozentsatz des mit FILLFACTOR angegebenen freien Speicherplatzes wird für die Zwischenebenenseiten des Index angewendet.

  • OFF oder fillfactor ist nicht angegeben.

    Die Zwischenebenenseiten werden nahezu vollständig aufgefüllt, wobei jedoch ausreichend freier Speicherplatz verfügbar bleibt, um mindestens eine Zeile in der maximal für diesen Index gültigen Größe aufzunehmen, die sich aus der Schlüsselmenge auf den Zwischenseiten ergibt.

FILLFACTOR = fillfactor

Gilt für: SQL Server 2008 (10.0.x) und höhere Versionen

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

Hinweis

Die Füllfaktorwerte 0 und 100 sind in jeglicher Hinsicht identisch.

IGNORE_DUP_KEY = { ON | OFF }

Gibt den Antworttyp an, wenn ein Einfügevorgang versucht, doppelte Schlüsselwerte in einen eindeutigen Index einzufügen. Die IGNORE_DUP_KEY-Option gilt nur für Einfügevorgänge nach dem Erstellen oder Neuerstellen des Index. Beim Ausführen von CREATE INDEX, ALTER INDEX oder UPDATE hat die Option keine Auswirkungen. Der Standardwert 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. Der gesamte INSERT Vorgang wird zurückgesetzt.

IGNORE_DUP_KEY kann nicht für Indizes festgelegt ON werden, die in einer Ansicht, nicht Indizes, XML-Indizes, räumlichen Indizes und gefilterten Indizes erstellt wurden.

Verwenden Sie sys.indexes, um IGNORE_DUP_KEY anzuzeigen.

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

STATISTICS_NORECOMPUTE = { ON | OFF }

Deaktivieren oder aktivieren Sie die Option AUTO_STATISTICS_UPDATEfür die automatische Statistikaktualisierung für die Statistiken im Zusammenhang mit den angegebenen Indizes. Der Standardwert ist OFF.

  • ON

    Automatische Statistikaktualisierungen werden deaktiviert, nachdem der Index neu erstellt wurde.

  • OFF

    Automatische Statistikaktualisierungen werden aktiviert, nachdem der Index neu erstellt wurde.

Um die automatische Aktualisierung von Statistiken wiederherzustellen, legen Sie den STATISTICS_NORECOMPUTE Befehl auf OFFoder wird ohne die NORECOMPUTE Klausel ausgeführtUPDATE STATISTICS.

Warnung

Wenn Sie die automatische Aktualisierung von Statistiken deaktivieren, wird möglicherweise verhindert, dass der Abfrageoptimierer optimale Ausführungspläne für Abfragen auswählt, die die Tabelle umfassen. Sie sollten diese Option sparsam und nur von einem qualifizierten Datenbankadministrator verwenden.

Diese Einstellung verhindert nicht, dass während des Neuerstellungsvorgangs ein automatisches Update mit vollständigem Scan der indexbezogenen Statistiken ausgeführt wird.

ALLOW_ROW_LOCKS = { ON | OFF }

Gilt für: SQL Server 2008 (10.0.x) und höhere Versionen

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

  • EIN

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

  • OFF

    Zeilensperren werden nicht verwendet.

ALLOW_PAGE_LOCKS = { ON | OFF }

Gilt für: SQL Server 2008 (10.0.x) und höhere Versionen

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

  • EIN

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

  • OFF

    Seitensperren werden nicht verwendet.

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }

Gilt für: SQL Server 2019 (15.x) und höhere Versionen

Gibt an, ob der Konflikt beim Einfügen der letzten Seite optimiert werden soll. Der Standardwert ist OFF. Weitere Informationen finden Sie im Abschnitt "Sequenzielle Schlüssel " des CREATE INDEX Artikels.

SORT_IN_TEMPDB = { ON | OFF }

Gilt für: SQL Server 2008 (10.0.x) und höhere Versionen

Gibt an, ob Sortierergebnisse gespeichert tempdbwerden sollen. Der Standardwert ist OFF.

  • ON

    Die Zwischensortierungsergebnisse, die zum Erstellen des Indexes verwendet werden, werden in tempdbgespeichert. Dies kann die Zeit verringern, die zum Erstellen eines Index erforderlich ist, wenn tempdb sich eine andere Gruppe von Datenträgern als die Benutzerdatenbank befindet. Sie erhöht jedoch den Betrag an Speicherplatz, der während der Indexerstellung verwendet wird.

  • OFF

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

ONLINE = { ON | OFF }

Gilt für: SQL Server 2008 (10.0.x) und höhere Versionen

Gibt an, ob die zugrunde liegenden Tabellen und zugeordneten Indizes für Abfragen und Datenänderungen während des Indexvorgangs verfügbar sind. Der Standardwert ist OFF. REBUILD kann als ONLINE Vorgang ausgeführt werden.

Hinweis

Eindeutige nicht gruppierte Indizes können nicht online erstellt werden. Dies schließt Indizes ein, die aufgrund einer UNIQUE Oder PRIMARY KEY Einschränkung erstellt werden.

  • ON

    Lang andauernde Tabellensperren werden während des Indexvorgangs nicht aufrechterhalten. Während der Hauptphase des Indexvorgangs wird nur eine beabsichtigte freigegebene Sperre für die Quelltabelle aufrechterhalten. Dadurch können Abfragen oder Updates für die zugrunde liegende Tabelle und die Indizes fortgesetzt werden. Zu Beginn des Vorgangs wird eine Freigegebene (S)-Sperre für einen kurzen Zeitraum auf dem Quellobjekt gespeichert. Am Ende des Vorgangs wird für einen kurzen Zeitraum eine S(Shared)-Sperre für die Quelle abgerufen, wenn ein nicht gruppierter Index erstellt wird; oder eine Sch-M -Sperre (Schemaänderung) wird abgerufen, wenn ein gruppierter Index online erstellt oder abgelegt wird und ein gruppierter oder nicht gruppierter Index neu erstellt wird. Obwohl die Onlineindexsperren kurze Metadatensperren sind, muss insbesondere die Sch-M-Sperre warten, bis alle blockierenden Transaktionen für diese Tabelle abgeschlossen sind. Während der Wartezeit sperrt die Sch-M-Sperre alle anderen Transaktionen, die an dieser Sperre warten, wenn sie auf die gleiche Tabelle zugreifen. ONLINE kann nicht festgelegt ON werden, wenn ein Index in einer lokalen temporären Tabelle erstellt wird.

    Hinweis

    Durch Neuerstellung von Onlineindizes können die low_priority_lock_wait-Optionen festgelegt werden, die weiter unten in diesem Abschnitt beschrieben werden. low_priority_lock_wait verwaltet die Priorität der S- und Sch-M-Sperren während der Onlineneuerstellung des Indexes.

  • OFF

    Tabellensperren werden während des Indexvorgangs angewandt. Dadurch wird verhindert, dass Benutzer während des Vorgangs auf die zugrunde liegende Tabelle zugreifen können. Ein Offlineindexvorgang, bei dem ein gruppierter Index erstellt, neu erstellt oder gelöscht bzw. ein nicht gruppierter Index neu erstellt oder gelöscht wird, aktiviert eine Schemaänderungssperre (SCH-M) für die Tabelle. Dadurch wird verhindert, dass Benutzer während des Vorgangs auf die zugrunde liegende Tabelle zugreifen können. Ein Offlineindexvorgang, bei dem ein nicht gruppierter Index erstellt wird, aktiviert eine freigegebene Sperre (S) für die Tabelle. Dadurch werden Aktualisierungen der zugrunde liegenden Tabelle verhindert, aber Lesevorgänge wie SELECT Anweisungen erlaubt.

Weitere Informationen finden Sie unter Funktionsweise von Onlineindexvorgängen.

Hinweis

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

RESUMABLE = { ON | OFF}

Gilt für: SQL Server 2022 (16.x) und höhere Versionen

Gibt an, ob ein ALTER TABLE ADD CONSTRAINT-Vorgang fortsetzbar ist. Das Hinzufügen eines Tabellenconstraints kann bei ON fortgesetzt werden. Das Hinzufügen eines Tabellenconstraints kann bei OFF nicht fortgesetzt werden. Der Standardwert ist OFF. Wenn die Option RESUMABLE auf ON festgelegt ist, ist die Option ONLINE = ON erforderlich.

MAX_DURATION bei Verwendung mit RESUMABLE = ON (erforderlich ONLINE = ON) gibt zeit (einen ganzzahligen Wert in Minuten) an, dass ein reaktivierbarer Online-Add-Einschränkungsvorgang ausgeführt wird, bevor er angehalten wird. Wenn nicht angegeben, wird der Vorgang bis zum Abschluss fortgesetzt. MAXDOP wird ebenfalls unterstützt RESUMABLE = ON .

Weitere Informationen zum Aktivieren und Verwenden von fortsetzbaren ALTER TABLE ADD CONSTRAINT-Vorgängen finden Sie unter Fortsetzbares Hinzufügen von Tabellenconstraints.

MAXDOP = max_degree_of_parallelism

Gilt für: SQL Server 2008 (10.0.x) und höhere Versionen

Überschreibt die Konfigurationsoption Max. Grad an Parallelität während des Indexvorgangs. Weitere Informationen finden Sie unter Konfigurieren des maximalen Parallelitätsgrads (Serverkonfigurationsoption). Wird verwendet MAXDOP , um die Anzahl der Prozessoren zu begrenzen, die in einer parallelen Planausführung verwendet werden. Maximal sind 64 Prozessoren zulässig.

max_degree_of_parallelism kann folgende Werte haben:

  • 1: Unterdrückt die parallele Plangenerierung.
  • >1: Beschränkt die maximale Anzahl von Prozessoren, die in einem parallelen Indexvorgang verwendet werden, auf die angegebene Zahl.
  • 0 (Standard): Verwendet die tatsächliche Anzahl von Prozessoren oder weniger basierend auf der aktuellen Systemauslastung.

Weitere Informationen finden Sie unter Konfigurieren von Parallelindexvorgängen.

Hinweis

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

DATA_COMPRESSION

Gilt für: SQL Server 2008 (10.0.x) und höhere Versionen

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

  • Keine

    Die Tabelle oder die angegebenen Partitionen werden nicht komprimiert. Gilt nur für rowstore-Tabellen und nicht für columnstore-Tabellen.

  • ROW

    Die Tabelle oder die angegebenen Partitionen werden mit Zeilenkomprimierung komprimiert. Gilt nur für rowstore-Tabellen und nicht für columnstore-Tabellen.

  • PAGE

    Die Tabelle oder die angegebenen Partitionen werden mit Seitenkomprimierung komprimiert. Gilt nur für rowstore-Tabellen und nicht für columnstore-Tabellen.

  • COLUMNSTORE

    Gilt für: SQL Server 2014 (12.x) und höhere Versionen

    Gilt nur für columnstore-Tabellen. COLUMNSTORE Gibt an, eine Partition zu dekomprimieren, die mit der COLUMNSTORE_ARCHIVE Option komprimiert wurde. Wenn die Daten wiederhergestellt werden, wird der COLUMNSTORE Index weiterhin mit der Columnstore-Komprimierung komprimiert, die für alle Spaltenspeichertabellen verwendet wird.

  • COLUMNSTORE_ARCHIVE

    Gilt für: SQL Server 2014 (12.x) und höhere Versionen

    Gilt nur für columnstore-Tabellen. Dies sind Tabellen, die mit einem gruppierten columnstore-Index gespeichert wurden. COLUMNSTORE_ARCHIVE komprimiert die angegebene Partition weiter auf eine kleinere Größe. Dies empfiehlt sich bei der Archivierung und in Situationen, in denen es auf eine geringere Speicherbelegung und nicht auf den zusätzlichen Zeitaufwand für das Speichern und Abrufen ankommt.

Weitere Informationen zur Komprimierung finden Sie unter Datenkomprimierung.

XML_COMPRESSION

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

Gibt die XML-Komprimierungsoption für Spalten mit dem Datentyp xml in der Tabelle an. Die folgenden Optionen sind verfügbar:

  • EIN

    Spalten mit dem Datentyp xml werden komprimiert.

  • OFF

    Spalten mit dem Datentyp xml werden nicht komprimiert.

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

Gilt für: SQL Server 2008 (10.0.x) und höhere Versionen

Gibt die Partitionen an, für die die Einstellung DATA_COMPRESSION oder XML_COMPRESSION gilt. Wenn die Tabelle nicht partitioniert ist, generiert das ON PARTITIONS Argument einen Fehler. Wenn die ON PARTITIONS Klausel nicht bereitgestellt wird, gilt die DATA_COMPRESSION Option für XML_COMPRESSION alle Partitionen einer partitionierten Tabelle.

<partition_number_expression> kann wie folgt angegeben werden:

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

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

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

--For rowstore tables
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)
)

--For columnstore tables
REBUILD WITH
(
  DATA_COMPRESSION = COLUMNSTORE ON PARTITIONS (1, 3, 5),
  DATA_COMPRESSION = COLUMNSTORE_ARCHIVE ON PARTITIONS (2, 4, 6 TO 8)
)

<single_partition_rebuild__option>

In den meisten Fällen werden bei der Neuerstellung eines Indexes auch alle Partitionen eines partitionierten Indexes neu erstellt. Die folgenden Optionen erstellen nicht alle Partitionen neu, wenn sie auf eine einzelne Partition angewendet werden.

  • SORT_IN_TEMPDB
  • MAXDOP
  • DATA_COMPRESSION
  • XML_COMPRESSION

low_priority_lock_wait

Gilt für: SQL Server 2014 (12.x) und höhere Versionen

Eine SWITCH oder eine Onlineindexerstellung wird abgeschlossen, sobald keine Blockierungsvorgänge für diese Tabelle vorhanden sind. WAIT_AT_LOW_PRIORITY gibt an, dass, wenn der Vorgang zur Neuerstellung des SWITCH Onlineindexes nicht sofort abgeschlossen werden kann, wartet es. Der Vorgang hält Sperren mit niedriger Priorität an und lässt die Fortsetzung anderer Vorgänge zu, die Sperren enthalten, die mit der DDL-Anweisung in Konflikt stehen. Das Weglassen der WAIT AT LOW PRIORITY-Option ist gleichwertig mit WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE).

MAX_DURATION = Zeit [ MINUTEN ]

Die Wartezeit (ein ganzzahliger Wert, der in Minuten angegeben ist), für die die SWITCH Sperre des Onlineindexes neu erstellt werden muss, wartet beim Ausführen des DDL-Befehls. Der SWITCH Vorgang zum Neuerstellen oder Onlineindex versucht sofort abzuschließen. Wenn der Vorgang für die MAX_DURATION Zeit blockiert wird, wird eine der ABORT_AFTER_WAIT Aktionen ausgeführt. MAX_DURATION Die Zeit ist immer in Minuten, und das Wort MINUTES kann weggelassen werden.

ABORT_AFTER_WAIT = { NONE | SELF | BLOCKER }

  • NONE

    Setzt den Vorgang zum Neuerstellen oder SWITCH Onlineindex fort, ohne die Sperrpriorität zu ändern (mit normaler Priorität).

  • SELF

    Beendet den DDL-Vorgang, der SWITCH derzeit ausgeführt wird, ohne eine Aktion auszuführen.

  • BLOCKERS

    Beendet alle Benutzertransaktionen, die den SWITCH DDL-Vorgang derzeit blockieren oder den DDL-Vorgang neu erstellen, damit der Vorgang fortgesetzt werden kann.

    BLOCKERS erfordert die ALTER ANY CONNECTION Berechtigung.

Zugehöriger Inhalt