ALTER TABLE index_option (Transact-SQL)
Gilt für: SQL Server Azure SQL-Datenbank Azure SQL Managed Instance SQL-Datenbank in Microsoft Fabric
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_KEY
WITH IGNORE_DUP_KEY = ON
.
STATISTICS_NORECOMPUTE = { ON | OFF }
Deaktivieren oder aktivieren Sie die Option AUTO_STATISTICS_UPDATE
fü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 OFF
oder 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 tempdb
werden sollen. Der Standardwert ist OFF
.
ON
Die Zwischensortierungsergebnisse, die zum Erstellen des Indexes verwendet werden, werden in
tempdb
gespeichert. Dies kann die Zeit verringern, die zum Erstellen eines Index erforderlich ist, wenntempdb
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 festgelegtON
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 derCOLUMNSTORE_ARCHIVE
Option komprimiert wurde. Wenn die Daten wiederhergestellt werden, wird derCOLUMNSTORE
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 dieALTER ANY CONNECTION
Berechtigung.