CREATE INDEX (Transact-SQL)

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Erstellt einen eindeutigen Index für eine Tabelle oder Sicht. Wird auch als Rowstore-Index bezeichnet, da es sich entweder um einen gruppierten oder nicht gruppierten B-Strukturindex handelt. Sie können noch bevor die Tabelle mit Daten aufgefüllt wird, einen Rowstore-Index erstellen. Verwenden Sie einen Rowstore-Index, um die Abfrageleistung zu verbessern, insbesondere, wenn die Abfragen aus bestimmten Spalten auswählen oder Werte erfordern, die in einer bestimmten Reihenfolge sortiert werden sollen.

Azure Synapse Analytics und Analytics Platform System (PDW) unterstützen UNIQUE-Einschränkungen derzeit nicht. Beispiele, die auf UNIQUE-Einschränkungen verweisen, gelten nur für SQL Server und SQL-Datenbank.

Weitere Informationen zu den Richtlinien für das Entwerfen von Indizes finden Sie im Handbuch zum SQL Server-Indexentwurf.

Beispiele:

1. Erstellen eines nicht gruppierten Index in einer Tabelle oder Ansicht

   CREATE INDEX index1 ON schema1.table1 (column1);
   

2. Erstellen eines gruppierten Index in einer Tabelle und Verwenden eines 3-teiligen Namens für die Tabelle

   CREATE CLUSTERED INDEX index1 ON database1.schema1.table1 (column1);
   

3. Erstellen eines nicht gruppierten Index mit einer UNIQUE-Einschränkung und Angeben der Sortierreihenfolge

   CREATE UNIQUE INDEX index1 ON schema1.table1 (column1 DESC, column2 ASC, column3 DESC);
   

Wichtiges Szenario:

Ab SQL Server 2016 (13.x) und SQL-Datenbank können Sie einen nicht gruppierten Index für einen Columnstore-Index verwenden, um die Abfrageleistung im Data Warehouse zu verbessern. Weitere Informationen finden Sie unter Columnstore-Indizes: Data Warehouse.

Informationen zu weiteren Indextypen finden Sie unter:

• CREATE XML INDEX
• CREATE SPATIAL INDEX
• CREATE COLUMNSTORE INDEX

Transact-SQL-Syntaxkonventionen

Syntax

Syntax für SQL Server, Azure SQL-Datenbank und Azure SQL Managed Instance

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON <object> ( column [ ASC | DESC ] [ ,...n ] )
    [ INCLUDE ( column_name [ ,...n ] ) ]
    [ WHERE <filter_predicate> ]
    [ WITH ( <relational_index_option> [ ,...n ] ) ]
    [ ON { partition_scheme_name ( column_name )
         | filegroup_name
         | default
         }
    ]
    [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]

[ ; ]

<object> ::=
{ database_name.schema_name.table_or_view_name | schema_name.table_or_view_name | table_or_view_name }

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

<filter_predicate> ::=
    <conjunct> [ AND ] [ ...n ]

<conjunct> ::=
    <disjunct> | <comparison>

<disjunct> ::=
        column_name IN (constant ,...n)

<comparison> ::=
        column_name <comparison_op> constant

<comparison_op> ::=
    { IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !< }

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

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

Abwärtskompatibler relationaler Index

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON <object> ( column_name [ ASC | DESC ] [ ,...n ] )
    [ WITH <backward_compatible_index_option> [ ,...n ] ]
    [ ON { filegroup_name | "default" } ]

<object> ::=
{
    [ database_name. [ owner_name ] . | owner_name. ]
    table_or_view_name
}

<backward_compatible_index_option> ::=
{
    PAD_INDEX
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB
  | IGNORE_DUP_KEY
  | STATISTICS_NORECOMPUTE
  | DROP_EXISTING
}

Syntax für Azure Synapse Analytics und Parallel Data Warehouse

CREATE CLUSTERED COLUMNSTORE INDEX index_name
    ON [ database_name . [ schema ] . | schema . ] table_name
    [ORDER (column[,...n])]
    [WITH ( DROP_EXISTING = { ON | OFF } )]
[;]


CREATE [ CLUSTERED | NONCLUSTERED ] INDEX index_name
    ON [ database_name . [ schema ] . | schema . ] table_name
        ( { column [ ASC | DESC ] } [ ,...n ] )
    WITH ( DROP_EXISTING = { ON | OFF } )
[;]

Argumente

UNIQUE

Erstellt einen eindeutigen Index für eine Tabelle oder Sicht. Ein eindeutiger Index ist ein Index, bei dem zwei Zeilen nicht den gleichen Indexschlüsselwert haben dürfen. Ein gruppierter Index für eine Sicht muss eindeutig sein.

Die Datenbank-Engine lässt das Erstellen eines eindeutigen Index für Spalten, die bereits doppelte Werte enthalten, nicht zu. Dies ist unabhängig davon, ob IGNORE_DUP_KEY auf ON festgelegt ist. Wenn Sie dies versuchen, wird in Datenbank-Engine eine Fehlermeldung angezeigt. Doppelte Werte müssen entfernt werden, bevor ein eindeutiger Index für die Spalte oder Spalten erstellt werden kann. In einem eindeutigen Index verwendete Spalten müssen auf NOT NULL festgelegt werden, da mehrere NULL-Werte beim Erstellen eines eindeutigen Index als Duplikate angesehen werden.

CLUSTERED

Erstellt einen Index, in dem die logische Reihenfolge der Schlüsselwerte die physische Reihenfolge der entsprechenden Zeilen in einer Tabelle bestimmt. Die unterste Ebene oder Blattebene des gruppierten Indexes enthält die eigentlichen Datenzeilen der Tabelle. Eine Tabelle oder Sicht kann nur über jeweils einen gruppierten Index verfügen.

Eine Sicht mit einem eindeutigen gruppierten Index wird als indizierte Sicht bezeichnet. Durch das Erstellen eines eindeutigen gruppierten Index für eine Sicht wird die Sicht physisch materialisiert. Ein eindeutiger gruppierter Index muss für eine Sicht erstellt werden, bevor ein anderer Index für dieselbe Sicht definiert werden kann. Weitere Informationen finden Sie unter Erstellen von indizierten Sichten.

Erstellen Sie den gruppierten Index, bevor Sie irgendeinen nicht gruppierten Index erstellen. Für eine Tabelle vorhandene nicht gruppierte Indizes werden neu erstellt, wenn ein gruppierter Index erstellt wird.

Falls CLUSTERED nicht angegeben ist, wird ein nicht gruppierter Index erstellt.

In einigen Fällen können durch das Erstellen eines gruppierten Indexes zuvor deaktivierte Indizes aktiviert werden. Weitere Informationen finden Sie unter Aktivieren von Indizes und Einschränkungen und Deaktivieren von Indizes und Einschränkungen.

NONCLUSTERED

Erstellt einen Index, der die logische Reihenfolge einer Tabelle angibt. Bei einem nicht gruppierten Index ist die physische Reihenfolge der Datenzeilen unabhängig von deren indizierter Reihenfolge.

Jede Tabelle kann über bis zu 999 nicht gruppierte Indizes verfügen, unabhängig davon, wie die Indizes erstellt werden: implizit mit PRIMARY KEY- und UNIQUE-Einschränkungen oder explizit mit CREATE INDEX.

Für indizierte Sichten können nicht gruppierte Indizes nur erstellt werden, wenn bereits ein eindeutiger gruppierter Index für die entsprechende Sicht definiert ist.

Wenn nicht anders angegeben, ist NONCLUSTERED der Standardindextyp.

index_name

Der Name des Index. 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.

column

Gibt die Spalten an, auf denen der Index basiert. Geben Sie zwei oder mehr Spaltennamen an, um einen zusammengesetzten Index für die kombinierten Werte der angegebenen Spalten zu erstellen. Führen Sie die Spalten, die im zusammengesetzten Index enthalten sein sollen, in der Reihenfolge der Sortierpriorität in den Klammern hinter table_or_view_name auf.

Es können bis zu 32 Spalten in einem einzigen zusammengesetzten Indexschlüssel kombiniert werden. Alle Spalten in einem zusammengesetzten Indexschlüssel müssen sich in derselben Tabelle oder Sicht befinden. Die maximal zulässige Größe der kombinierten Indexwerte liegt bei 900 Byte für gruppierte Indizes oder 1700 Byte für nicht gruppierte Indizes. Für Versionen vor SQL-Datenbank und SQL Server 2016 (13.x) liegen die Höchstwerte bei 16 Spalten und 900 Byte.

Spalten mit den LOB-Datentypen (Large Object) ntext, text, varchar(max), nvarchar(max), varbinary(max), xml oder image können nicht als Schlüsselspalten für einen Index angegeben werden. Darüber hinaus darf eine Sichtdefinition keine Spalten der Datentypen ntext, text oder image enthalten, auch wenn in der CREATE INDEX-Anweisung nicht auf diese Spalten verwiesen wird.

Sie können Indizes für Spalten mit dem CLR-benutzerdefinierten Typ erstellen, wenn durch den Typ Binärreihenfolgen unterstützt werden. Außerdem können Sie Indizes für berechnete Spalten erstellen, die als Methodenaufrufe aus einer Spalte mit dem benutzerdefinierten Typ definiert sind, vorausgesetzt, die Methoden sind als deterministisch markiert und führen keine Datenzugriffe durch. Weitere Informationen zum Indizieren von Spalten mit dem CLR-benutzerdefinierten Typ finden Sie unter CLR-benutzerdefinierte Typen.

[ ASC | DESC ]

Bestimmt für die entsprechende Indexspalte die aufsteigende oder absteigende Sortierreihenfolge. Die Standardeinstellung ist ASC.

INCLUDE (column [ ,... n ] )

Gibt die Nichtschlüsselspalten an, die zur Blattebene des nicht gruppierten Index hinzugefügt werden sollen. Der nicht gruppierte Index kann eindeutig oder nicht eindeutig sein.

Spaltennamen dürfen in der INCLUDE-Liste nicht wiederholt und nicht gleichzeitig als Schlüssel- und Nichtschlüsselspalten verwendet werden. Nicht gruppierte Indizes enthalten stets die Spalten des gruppierten Index, wenn ein gruppierter Index für die Tabelle definiert ist. Weitere Informationen finden Sie unter Create Indexes with Included Columns.

Mit Ausnahme von text, ntextund imagesind alle Datentypen zulässig. Ab SQL Server 2012 (11.x) und Azure SQL-Datenbank kann der Index mithilfe der ONLINE-Option erstellt oder neu erstellt werden, wenn eine der angegebenen Nichtschlüsselspalten die Datentypen varchar(max), nvarchar(max) oder varbinary(max) aufweist.

Bei berechneten Spalten, die deterministisch und präzise oder unpräzise sind, kann es sich um eingeschlossene Spalten handeln. Berechnete Spalten, die aus den Datentypen image, ntext, text, varchar(max) , nvarchar(max) , varbinary(max) und xml abgeleitet wurden, können, solange der Datentyp der berechneten Spalte als Indexschlüsselspalte zulässig ist, in Spalten eingefügt werden, bei denen es sich nicht um Schlüsselspalten handelt. Weitere Informationen finden Sie unter Indexes on Computed Columns.

Weitere Informationen zum Erstellen eines XML-Index finden Sie unter CREATE XML INDEX.

WHERE <filter_predicate>

Erstellt einen gefilterten Index, wobei angegeben wird, welche Zeilen in den Index aufgenommen werden sollen. Der gefilterte Index muss ein nicht gruppierter Index für eine Tabelle sein. Erstellt gefilterte Statistikdaten für die Datenzeilen im gefilterten Index.

Im Filterprädikat werden einfache Vergleichsoperatoren verwendet. Es darf darin nicht auf eine berechnete Spalte, eine UDT-Spalte, eine Spalte mit einem räumlichen Datentyp oder eine Spalten mit dem hierarchyID-Datentyp verwiesen werden. Vergleiche mit NULL-Literalen sind mit den Vergleichsoperatoren nicht zulässig. Verwenden Sie stattdessen die Operatoren IS NULL und IS NOT NULL.

Es folgen einige Beispiele für Filterprädikate für die Production.BillOfMaterials-Tabelle:

WHERE StartDate > '20000101' AND EndDate <= '20000630'

WHERE ComponentID IN (533, 324, 753)

WHERE StartDate IN ('20000404', '20000905') AND EndDate IS NOT NULL

Gefilterte Indizes gelten nicht für XML-Indizes und Volltextindizes. Bei UNIQUE-Indizes müssen nur die ausgewählten Zeilen über eindeutige Indexwerte verfügen. Bei gefilterten Indizes ist die IGNORE_DUP_KEY-Option nicht zulässig.

ON partition_scheme_name ( column_name )

Gibt das Partitionsschema an, das die Dateigruppen definiert, denen die Partitionen eines partitionierten Index zugeordnet werden. Das Partitionsschema muss bereits durch Ausführen von CREATE PARTITION SCHEME oder ALTER PARTITION SCHEME in der Datenbank vorhanden sein. column_name gibt die Spalte an, auf deren Grundlage ein partitionierter Index partitioniert wird. Diese Spalte muss mit dem Datentyp, der Länge und der Genauigkeit des Arguments der Partitionsfunktion übereinstimmen, die partition_scheme_name verwendet. column_name ist nicht auf die Spalten in der Indexdefinition beschränkt. Es können beliebige Spalten der Basistabelle angegeben werden, mit der Ausnahme, dass column_name beim Partitionieren von UNIQUE-Indizes aus den Spalten ausgewählt werden muss, die als eindeutige Schlüssel verwendet werden. Mit dieser Einschränkung kann Datenbank-Engine die Eindeutigkeit der Schlüsselwerte in nur einer einzigen Partition überprüfen.

Wenn partition_scheme_name oder filegroup bei einer partitionierten Tabelle nicht angegeben werden, wird der Index im selben Partitionsschema platziert, und er verwendet dieselbe Partitionierungsspalte wie die zugrunde liegende Tabelle.

Weitere Informationen zur Partitionierung von Indizes finden Sie unter Partitionierte Tabellen und Indizes.

ON filegroup_name

Erstellt den angegebenen Index für die angegebene Dateigruppe. Wenn kein Speicherort angegeben wurde und die Tabelle oder Sicht nicht partitioniert ist, verwendet der Index dieselbe Dateigruppe wie die zugrunde liegende Tabelle oder Sicht. Die Dateigruppe muss bereits vorhanden sein.

ON "default"

Erstellt den angegebenen Index in derselben Dateigruppe oder in demselben Partitionsschema wie die Tabelle oder Sicht.

Die Benennung „default“ ist in diesem Kontext kein Schlüsselwort. Dabei handelt es sich um einen Bezeichner für die Standarddateigruppe, der wie in ON "default" oder ON [default] abgegrenzt sein muss. Wenn "default" angegeben wird, muss die Option QUOTED_IDENTIFIER für die aktuelle Sitzung auf ON festgelegt sein. Dies ist die Standardeinstellung. Weitere Informationen finden Sie unter SET QUOTED_IDENTIFIER.

[ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]

Gibt die Platzierung der FILESTREAM-Daten für die Tabelle an, wenn ein gruppierter Index erstellt wird. Die FILESTREAM_ON-Klausel lässt zu, dass FILESTREAM-Daten in eine andere FILESTREAM-Dateigruppe oder ein anderes Partitionsschema verschoben werden.

filestream_filegroup_name ist der Name einer FILESTREAM-Dateigruppe. Für die Dateigruppe muss eine Datei mit einer CREATE DATABASE-Anweisung oder einer ALTER DATABASE-Anweisung definiert worden sein, andernfalls wird ein Fehler ausgelöst.

Wenn die Tabelle partitioniert ist, muss die FILESTREAM_ON-Klausel eingeschlossen werden und ein Partitionsschema von FILESTREAM-Dateigruppen angeben, das die gleiche Partitionsfunktion und die gleichen Partitionsspalten wie das Partitionsschema der Tabelle enthält. Andernfalls wird ein Fehler ausgelöst.

Wenn die Tabelle nicht partitioniert ist, kann die FILESTREAM-Spalte nicht partitioniert werden. Die FILESTREAM-Daten für die Tabelle müssen in einer einzigen Dateigruppe gespeichert werden, die in der FILESTREAM_ON-Klausel angegeben wird.

FILESTREAM_ON NULL kann in einer CREATE INDEX-Anweisung angegeben werden, wenn ein gruppierter Index erstellt wird und die Tabelle keine FILESTREAM-Spalte enthält.

Weitere Informationen finden Sie unter FILESTREAM (SQL Server).

<object>::=

Das vollqualifizierte oder nicht vollqualifizierte Objekt, das indiziert werden soll.

database_name

Der Name der Datenbank.

schema_name

Der Name des Schemas, zu dem die Tabelle oder Sicht gehören.

table_or_view_name

Der Name der Tabelle oder Sicht, die indiziert werden soll.

Die Sicht muss mit SCHEMABINDING definiert werden, um einen Index für sie zu erstellten. Ein eindeutiger gruppierter Index muss für eine Sicht erstellt werden, bevor ein nicht gruppierter Index erstellt wird. Weitere Informationen zu indizierten Sichten finden Sie im Abschnitt mit den Hinweisen.

Ab SQL Server 2016 (13.x) kann es sich bei dem Objekt um eine Tabelle handeln, die gemeinsam mit einem gruppierten Columnstore-Index gespeichert wird.

Azure SQL-Datenbank unterstützt das aus drei Teilen bestehende Namensformat database_name.[schema_name].object_name, wenn database_name die aktuelle Datenbank bzw. database_nametempdb ist und object_name mit # beginnt.

<relational_index_option>::=

Gibt die Optionen an, die beim Erstellen des Indexes verwendet werden sollen.

PAD_INDEX = { ON | OFF }

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

EIN
Der Prozentsatz des mit fillfactor angegebenen freien Speicherplatzes wird für die Zwischenebenenseiten des Indexes angewendet.

OFF oder fillfactor ist nicht angegeben.
Die Zwischenebenenseiten sind nahezu vollständig aufgefüllt. Allerdings ist ausreichend Speicherplatz vorhanden, um mindestens eine Zeile in der maximal für den Index möglichen Größe aufzunehmen, wenn der Schlüsselsatz auf den Zwischenseiten berücksichtigt wird.

Die Option PAD_INDEX ist nur dann hilfreich, wenn FILLFACTOR angegeben ist, da PAD_INDEX den durch FILLFACTOR angegebenen Prozentsatz verwendet. Wenn der für FILLFACTOR angegebene Prozentsatz nicht groß genug ist, um eine Zeile aufzunehmen, überschreibt die Datenbank-Engine diesen Prozentsatz intern, um das Minimum zuzulassen. Auf jeder Zwischenindexseite befinden sich unabhängig vom angegebenen fillfactor-Wert nie weniger als zwei Zeilen.

In abwärtskompatibler Syntax entspricht WITH PAD_INDEXWITH PAD_INDEX = ON.

FILLFACTOR = fillfactor

Gibt einen Prozentsatz an, der angibt, wie weit das Datenbank-Engine die Blattebene jeder Indexseite während der Indexerstellung oder -neuerstellung füllen soll. Der Wert für fillfactor muss ein ganzzahliger Wert zwischen 1 und 100 sein. Die Füllfaktorwerte 0 und 100 sind in jeder Hinsicht identisch. Wenn fillfactor 100 ist, erstellt das Datenbank-Engine Indizes mit vollständig aufgefüllten Blattseiten.

Die FILLFACTOR-Einstellung gilt nur, wenn der Index erstellt oder neu erstellt wird. Die Datenbank-Engine hält den angegebenen Prozentsatz des Speicherplatzes auf den Seiten nicht dynamisch frei.

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

Weitere Informationen finden Sie unter Angeben des Füllfaktors für einen Index.

SORT_IN_TEMPDB = { ON | OFF }

Gibt an, ob temporäre Ergebnisse des Sortierens in tempdb gespeichert werden sollen. Außer für Azure SQL-Datenbank Hyperscale ist der Standardwert OFF. Für alle Indexerstellungsvorgänge in Hyperscale ist SORT_IN_TEMPDB unabhängig von der angegebenen Option immer auf ON festgelegt, sofern nicht die fortsetzbare Indexneuerstellung verwendet wird.

EIN
Die Zwischenergebnisse von Sortierungen, mit denen der Index erstellt wird, werden in tempdb gespeichert. Dadurch kann sich die zum Erstellen eines Index erforderliche Zeit verringern, wenn sich tempdb in anderen Datenträgersätzen befindet als die Benutzerdatenbank. 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.

Zusätzlich zu dem Speicherplatz, der in der Benutzerdatenbank zum Erstellen des Index erforderlich ist, muss tempdb ungefähr die gleiche Menge an zusätzlichem Speicherplatz aufweisen, um die Zwischenergebnisse der Sortierung zu speichern. Weitere Informationen finden Sie unter SORT_IN_TEMPDB-Option für Indizes.

In abwärtskompatibler Syntax entspricht WITH SORT_IN_TEMPDBWITH SORT_IN_TEMPDB = ON.

IGNORE_DUP_KEY = { ON | OFF }

Gibt die Fehlermeldung an, wenn ein Einfügevorgang versucht, doppelte Schlüsselwerte in einen eindeutigen Index einzufügen. Die IGNORE_DUP_KEY-Option gilt nur für Einfügevorgänge nach dem Erstellen oder Neuerstellen des Index. Beim Ausführen von CREATE INDEX, ALTER INDEX oder UPDATE hat die Option keine Auswirkungen. Der Standardwert ist OFF.

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

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}

Gibt an, ob Verteilungsstatistiken neu berechnet werden. Der Standardwert ist OFF.

EIN
Veraltete Indexstatistiken werden nicht automatisch neu berechnet.

OFF
Die automatischen Updates der Statistiken sind aktiviert.

Wenn Sie die automatische Aktualisierung von Statistiken wiederherstellen möchten, legen Sie STATISTICS_NORECOMPUTE auf OFF fest oder führen Sie die UPDATE STATISTICS-Anweisung ohne die NORECOMPUTE-Klausel aus.

In abwärtskompatibler Syntax entspricht WITH STATISTICS_NORECOMPUTEWITH STATISTICS_NORECOMPUTE = ON.

STATISTICS_INCREMENTAL = { ON | OFF }

Gilt für: SQL Server (ab SQL Server 2014 (12.x)) und Azure SQL-Datenbank

Bei ON wird die Statistik pro Partition erstellt. Bei OFF wird die Statistikstruktur gelöscht und SQL Server berechnet die Statistiken erneut. Der Standardwert ist OFF.

Wenn Statistiken pro Partition nicht unterstützt werden, wird die Option ignoriert und eine Warnung generiert. Inkrementelle Statistiken werden für folgende Statistiktypen nicht unterstützt:

• Statistiken, die mit Indizes erstellt wurden, die über keine Partitionsausrichtung mit der Basistabelle verfügen.
• Statistiken, die für lesbare sekundäre Always On-Datenbanken erstellt wurden.
• Statistiken, die für schreibgeschützte Datenbanken erstellt wurden.
• Statistiken, die für gefilterte Indizes erstellt wurden.
• Statistiken, die für Sichten erstellt wurden.
• Statistiken, die für interne Tabellen erstellt wurden.
• Statistiken, die mit räumlichen Indizes oder XML-Indizes erstellt wurden.

DROP_EXISTING = { ON | OFF }

Eine Option zum Entfernen und erneutem Erstellen eines vorhandenen gruppierten oder nicht gruppierten Index mit veränderten Spaltenspezifikationen, die den Namen für den Index beibehält. Der Standardwert ist OFF.

EIN
Gibt an, dass der vorhandene Index entfernt und neu erstellt werden soll. Der Index muss über denselben Namen wie der Parameter index_name verfügen.

OFF
Gibt an, dass der vorhandene Index nicht entfernt und neu erstellt werden soll. SQL Server zeigt einen Fehler an, wenn der angegebene Indexname bereits vorhanden ist.

Mit DROP_EXISTING können Sie Folgendes ändern:

• Umwandlung eines nicht gruppierten Rowstore-Index in einen gruppierten Rowstore-Index.

Sie können mit DROP_EXISTING Folgendes nicht ändern:

• Umwandlung eines gruppierten Rowstore-Index in einen nicht gruppierten Rowstore-Index.
• Umwandlung eines gruppierten Columnstore-Index in einen nicht gruppierten Rowstore-Index.

In abwärtskompatibler Syntax entspricht WITH DROP_EXISTINGWITH DROP_EXISTING = ON.

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. Der Standardwert ist OFF.

ON
Lang andauernde Sperren werden nicht für die Dauer des Indexvorgangs 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 für sehr kurze Zeit eine freigegebene Sperre (S) für das Quellobjekt aufrechterhalten. Am Ende des Vorgangs wird für kurze Zeit eine gemeinsame Sperre (S) für die Quelle aktiviert, wenn ein nicht gruppierter Index erstellt wird. Wenn ein gruppierter Index online erstellt oder gelöscht wird und wenn ein gruppierter oder nicht gruppierter Index neu erstellt wird, wird eine Schemaänderungssperre (Schema Modification, SCH-M) aktiviert. ONLINE kann nicht auf ON festgelegt werden, wenn ein Index für eine lokale temporäre Tabelle erstellt wird.

OFF
Die Tabellensperren werden für die Dauer des Indexvorgangs angewendet. Ein Offlineindexvorgang, 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 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 freigegebene Sperre (S) für die Tabelle. Dadurch werden Updates der zugrunde liegenden Tabelle verhindert. Lesevorgänge, wie SELECT-Anweisungen, sind jedoch zulässig.

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

Indizes, einschließlich Indizes globaler temporärer Tabellen, können online erstellt werden. Es gelten folgende Ausnahmen:

• XML-Index
• Index für eine lokale temp-Tabelle
• Eindeutiger gruppierter Ausgangsindex für eine Sicht
• Deaktivierte gruppierte Indizes
• Columnstore-Indizes
• Gruppierter Index, wenn die zugrunde liegende Tabelle LOB-Datentypen (image, ntext, text) und räumliche Datentypen enthält.
• varchar(max)- und varbinary(max)-Spalten können nicht Teil eines Indexschlüssels sein. In SQL Server (ab SQL Server 2012 (11.x)) und Azure SQL-Datenbank kann ein gruppierter Index mit der Option ONLINE erstellt oder neu erstellt werden, wenn eine Tabelle Spalten vom Typ varchar(max) oder varbinary(max) enthält.

Weitere Informationen finden Sie unter Funktionsweise von Onlineindexvorgängen.

RESUMABLE = { ON | OFF }

Gilt für: SQL Server (ab SQL Server 2019 (15.x)) und Azure SQL-Datenbank

Gibt an, ob ein Onlineindexvorgang fortsetzbar ist.

EIN
Der Indexvorgang ist fortsetzbar.

OFF
Der Indexvorgang ist nicht fortsetzbar.

MAX_DURATION = time [MINUTEN]; wird mit RESUMABLE = ON verwendet (erfordert ONLINE = ON)

Gilt für: SQL Server (ab SQL Server 2019 (15.x)) und Azure SQL-Datenbank

Gibt die Zeitspanne an (als ganzzahligen Wert in Minuten), in der ein fortsetzbarer Onlineindexvorgang ausgeführt wird, bevor er angehalten wird.

ALLOW_ROW_LOCKS = { ON | OFF }

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 }

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 (ab SQL Server 2019 (15.x)) und Azure SQL-Datenbank

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.

MAXDOP = max_degree_of_parallelism

Überschreibt die Konfigurationsoption max degree of parallelism (Max. Grad an Parallelität) für die Dauer des Indexvorgangs. Weitere Informationen finden Sie unter Konfigurieren der Serverkonfigurationsoption Max. Grad an Parallelität. Sie können mit MAXDOP die Anzahl der Prozessoren begrenzen, die bei der Ausführung paralleler Pläne verwendet werden. Maximal sind 64 Prozessoren zulässig.

max_degree_of_parallelism kann folgende Werte haben:

1
Unterdrückt das Generieren paralleler Pläne.

>1
Beschränkt die maximale Anzahl der Prozessoren, die bei einem parallelen Indexvorgang verwendet werden, je nach aktueller Systemauslastung auf die angegebene Zahl oder einen niedrigeren Wert.

0 (Standard)
Verwendet abhängig von der aktuellen Systemarbeitsauslastung die tatsächliche Anzahl von Prozessoren oder weniger Prozessoren.

Weitere Informationen finden Sie unter Konfigurieren von Parallelindexvorgängen.

DATA_COMPRESSION

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

Keine
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 Datenkomprimierung 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 den angegebenen Index an, der mindestens eine Spalte vom XML-Datentyp enthält. Die folgenden Optionen sind verfügbar:

EIN
Der Index oder die angegebenen Partitionen werden mit der XML-Komprimierung komprimiert.

OFF
Der Index oder die angegebenen Partitionen werden nicht komprimiert.

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

Gibt die Partitionen an, für die die Einstellung DATA_COMPRESSION oder XML_COMPRESSION gilt. Wenn der Index nicht partitioniert ist, erzeugt das Argument ON PARTITIONS einen Fehler. Wenn die ON PARTITIONS-Klausel nicht angegeben wird, gilt die DATA_COMPRESSION- oder XML_COMPRESSION-Option für alle Partitionen eines partitionierten Index.

<partition_number_expression> kann wie folgt angegeben werden:

• Geben Sie die Nummer für eine Partition an, beispielsweise: 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:

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)
);

Sie können die Option XML_COMPRESSION auch mehrmals angeben. Beispiel:

REBUILD WITH
(
  XML_COMPRESSION = OFF ON PARTITIONS (1),
  XML_COMPRESSION = ON ON PARTITIONS (2, 4, 6 TO 8),
  XML_COMPRESSION = OFF ON PARTITIONS (3, 5)
);

Bemerkungen

Die CREATE INDEX-Anweisung wird so optimiert wie jede andere Abfrage. Um weniger E/A-Vorgänge zu benötigen, entscheidet der Abfrageprozessor möglicherweise, einen anderen Index zu scannen, statt einen Tabellenscan auszuführen. Der Sortiervorgang wird in einigen Situationen möglicherweise umgangen. Auf einem Multiprozessorcomputer kann CREATE INDEX mehr Prozessoren verwenden, um die mit dem Erstellen des Index zusammenhängenden Scan- und Sortiervorgänge auszuführen. Dies geschieht in gleicher Weise wie für andere Abfragen. Weitere Informationen finden Sie unter Konfigurieren von Parallelindexvorgängen.

Der CREATE INDEX-Vorgang kann minimal protokolliert werden, wenn für die Datenbank das massenprotokollierte oder einfache Wiederherstellungsmodell festgelegt ist.

Indizes können für temporäre Tabellen erstellt werden. Wenn die Tabelle gelöscht oder die Sitzung beendet wird, werden die Indizes gelöscht.

Ein gruppierter Index kann auf einer Tabellenvariable erstellt werden, wenn ein Primärschlüssel generiert wird. Wenn die Abfrage abgeschlossen wird oder die Sitzung beendet wird, wird der Index gelöscht.

Durch Indizes werden erweiterte Eigenschaften unterstützt.

CREATE INDEX wird in Microsoft Fabric nicht unterstützt.

Gruppierte Indizes

Für das Erstellen eines gruppierten Index für eine Tabelle (Heap) oder das Löschen und Neuerstellen eines vorhandenen gruppierten Index muss zusätzlicher Arbeitsbereich in der Datenbank verfügbar sein, um das Sortieren von Daten und das Speichern einer temporären Kopie der ursprünglichen Tabelle oder von vorhandenen gruppierten Indexdaten zu ermöglichen. Weitere Informationen zu gruppierten Indizes finden Sie unter Erstellen gruppierter Indizes und im Leitfaden zur Architektur und zum Design von SQL Server-Indizes.

Nicht gruppierte Indizes

Ab SQL Server 2016 (13.x) und Azure SQL-Datenbank können Sie nicht gruppierte Indizes für eine Tabelle erstellen, die als gruppierter Columnstore-Index gespeichert wurde. Wenn Sie zuerst einen nicht gruppierten Index für eine Tabelle erstellen, die als Head oder gruppierter Index gespeichert ist, bleibt der Index erhalten, wenn Sie später die Tabelle in einen gruppierten Columnstore-Index konvertieren. Außerdem ist es nicht notwendig, den nicht gruppierten Index zu löschen, wenn Sie den gruppierten Columnstore-Index neu erstellen.

Einschränkungen:

• Die FILESTREAM_ON-Option gilt nicht, wenn Sie einen nicht gruppierten Index für eine Tabelle erstellen, die als gruppierter Columnstore-Index gespeichert wurde.

Eindeutige Indizes

Wenn ein eindeutiger Index vorhanden ist, prüft Datenbank-Engine jedes Mal, wenn Daten mithilfe von Einfügevorgängen hinzugefügt werden, auf doppelte Werte. Für Einfügevorgänge, die doppelte Schlüsselwerte generieren würden, wird ein Rollback ausgeführt. In Datenbank-Engine wird in diesem Fall eine Fehlermeldung angezeigt. Dies trifft auch dann zu, wenn beim Einfügevorgang viele Zeilen geändert werden, aber nur ein doppelter Wert verursacht wird. Wenn versucht wird, Daten einzugeben, für die ein eindeutiger Index vorhanden ist, und die IGNORE_DUP_KEY-Klausel auf ON festgelegt ist, schlagen nur die Zeilen fehl, die den UNIQUE-Index verletzen.

Partitionierten Indizes

Partitionierte Indizes werden ähnlich wie partitionierte Tabellen erstellt und verwaltet. Aber wie gewöhnliche Indizes werden sie wie separate Datenobjekte behandelt. Sie können einen partitionierten Index für eine nicht partitionierte Tabelle erstellen, und Sie können einen nicht partitionierten Index für eine partitionierte Tabelle erstellen.

Wenn Sie einen Index für eine partitionierte Tabelle erstellen und keine Dateigruppe angeben, in die der Index platziert werden soll, wird der Index auf die gleiche Weise partitioniert wie die zugrunde liegende Tabelle. Der Grund hierfür ist, dass Indizes standardmäßig in dieselben Dateigruppen wie die zugrunde liegenden Tabellen platziert werden. Bei partitionierten Tabellen werden Indizes in dasselbe Partitionsschema platziert, das dieselben Partitionierungsspalten verwendet. Wenn der Index das gleiche Partitionsschema und die gleiche Partitionierungsspalte wie die Tabelle verwendet, wird der Index auf die Tabelle ausgerichtet.

Beim Partitionieren eines nicht eindeutigen gruppierten Index fügt Datenbank-Engine standardmäßig alle Partitionierungsspalten zu der Liste der gruppierten Indexschlüssel hinzu, sofern sie dort noch nicht angegeben wurden.

Indizierte Sichten können für partitionierte Tabellen auf die gleiche Weise wie Indizes für Tabellen erstellt werden. Weitere Informationen zu gruppierten Indizes finden Sie unter Partitionierte Tabellen und Indizes und im Leitfaden zur Architektur und zum Design von SQL Server-Indizes.

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

Gefilterte Indizes

Ein gefilterter Index ist ein optimierter nicht gruppierter Index, der sich für Abfragen eignet, mit denen ein kleiner Prozentsatz von Zeilen in einer Tabelle ausgewählt wird. Es wird ein Filterprädikat verwendet, um einen Teil der Daten in der Tabelle zu indizieren. Ein gut entworfener gefilterter Index kann die Abfrageleistung verbessern, den Speicheraufwand verringern und Wartungskosten reduzieren.

Erforderliche SET-Optionen für gefilterte Indizes

Die SET-Optionen in der Spalte Erforderlicher Wert sind immer dann erforderlich, wenn eine der folgenden Bedingungen auftritt:

• Es wird ein gefilterter Index erstellt.

• Ein INSERT-, UPDATE-, DELETE- oder MERGE-Vorgang ändert die Daten in einem gefilterten Index.

• Der gefilterte Index wird vom Abfrageoptimierer verwendet, um den Abfrageplan zu erstellen.

  SET-Optionen	Erforderlicher Wert	Standardserverwert	Standard OLE DB- und ODBC-Wert	Standard DB-Library-Wert
  ANSI_NULLS	EIN	EIN	EIN	OFF
  ANSI_PADDING	EIN	EIN	EIN	OFF
  ANSI_WARNINGS*	EIN	EIN	EIN	OFF
  ARITHABORT	EIN	EIN	OFF	OFF
  CONCAT_NULL_YIELDS_NULL	EIN	EIN	EIN	OFF
  NUMERIC_ROUNDABORT	OFF	OFF	OFF	OFF
  QUOTED_IDENTIFIER	EIN	EIN	EIN	OFF

  • Durch Festlegen von ANSI_WARNINGS auf ON wird implizit ARITHABORT auf ON festgelegt, wenn der Kompatibilitätsgrad der Datenbank auf 90 oder höher festgelegt ist. Wird der Kompatibilitätsgrad der Datenbank auf 80 oder niedriger festgelegt, muss die ARITHABORT-Option explizit auf ON festgelegt werden.

Wenn die SET-Optionen falsch sind, können die folgenden Bedingungen auftreten:

• Der gefilterte Index wird nicht erstellt.
• Datenbank-Engine generiert einen Fehler und führt ein Rollback aller INSERT-, UPDATE-, DELETE- oder MERGE-Anweisungen aus, die im Index gespeicherte Daten ändern.
• Der Abfrageoptimierer berücksichtigt nicht den Index im Ausführungsplan für Transact-SQL-Anweisungen.

Weitere Informationen zu gruppierten Indizes finden Sie unter Erstellen gefilterter Indizes und im Leitfaden zur Architektur und zum Design von SQL Server-Indizes.

Räumlichkeitsindizes

Weitere Informationen zu räumlichen Indizes finden Sie unter CREATE SPATIAL INDEX und Übersicht über räumliche Indizes.

XML-Indizes

Weitere Informationen zu XML-Indizes finden Sie unter CREATE XML INDEX; und XML-Indizes (SQL Server).

Indexschlüsselgröße

Die maximale Größe für einen Indexschlüssel beträgt 900 Byte für gruppierte Indizes und 1700 Byte für nicht gruppierte Indizes. (Vor SQL-Datenbank und SQL Server 2016 (13.x) war der Grenzwert immer 900 Byte.) Indizes für Spalten des Datentyps varchar, deren Größe den Grenzwert überschreitet, können erstellt werden, wenn die in den Spalten enthaltenen Daten zum Zeitpunkt der Indexerstellung das Größenlimit nicht überschreiten. Allerdings treten bei nachfolgenden Einfüge- oder Updateaktionen für die Spalten Fehler auf, wenn dadurch das Limit überschritten wird. Der Indexschlüssel eines gruppierten Index kann keine Spalten des Datentyps varchar enthalten, bei denen Daten in der Zuordnungseinheit ROW_OVERFLOW_DATA vorhanden sind. Wird ein gruppierter Index für eine varchar-Spalte erstellt, bei der in der Zuordnungseinheit IN_ROW_DATA Daten vorhanden sind, erzeugen alle nachfolgenden Einfügungen und Updates der Spalte einen Fehler, bei der diese Daten aus der Zeile entfernt werden.

Nicht gruppierte Indizes können Nichtschlüsselspalten auf der Blattebene des Indexes enthalten. Diese Spalten werden von Datenbank-Engine beim Berechnen der Indexschlüsselgröße nicht berücksichtigt. Weitere Informationen finden Sie unter Erstellen von Indizes mit eingeschlossenen Spalten und im Leitfaden zur Architektur und zum Design von SQL Server-Indizes.

Berechnete Spalten

Indizes können für berechnete Spalten erstellt werden. Zudem können berechnete Spalten die Eigenschaft PERSISTED besitzen. Das bedeutet, dass Datenbank-Engine die berechneten Werte in der Tabelle speichert und sie aktualisiert, wenn andere Spalten, von denen die berechnete Spalte abhängt, aktualisiert werden. Datenbank-Engine verwendet diese persistenten Werte, wenn ein Index für die Spalte erstellt wird und wenn in einer Abfrage auf den Index verwiesen wird.

Zum Indizieren einer berechneten Spalte muss diese deterministisch und präzise sein. Allerdings wird mithilfe der PERSISTED-Eigenschaft der Typ der indizierbaren berechneten Spalten um Folgendes erweitert:

• Auf Transact-SQL- und CLR-Funktionen basierende berechnete Spalten und Methoden des CLR-benutzerdefinierten Typs, die vom Benutzer als deterministisch markiert sind.
• Berechnete Spalten, die auf Ausdrücken basieren, die gemäß der Definition von Datenbank-Engine deterministisch, aber unpräzise sind.

Für persistente berechnete Spalten müssen die folgenden SET-Optionen wie im vorherigen Abschnitt Erforderliche SET-Optionen für gefilterte Indizes gezeigt festgelegt werden.

Die jeweilige UNIQUE- oder PRIMARY KEY-Einschränkung kann eine berechnete Spalte enthalten, sofern diese alle Bedingungen für das Indizieren erfüllt. Die berechnete Spalte muss insbesondere deterministisch und präzise oder deterministisch und persistent sein. Weitere Informationen zu deterministischen Funktionen finden Sie unter Deterministische und nicht deterministische Funktionen.

Berechnete Spalten, die aus den Datentypen image, ntext, text, varchar(max) , nvarchar(max) , varbinary(max) und xml abgeleitet wurden, können entweder als Schlüssel oder eingefügte Spalte indiziert werden, bei der es sich nicht um eine Schlüsselspalte handelt, solange der Datentyp der berechneten Spalte als Indexschlüsselspalte oder Nichtschlüsselspalte zulässig ist. Sie können beispielsweise keinen primären XML-Index für eine berechnete xml-Spalte erstellen. Wenn der Indexschlüssel die zulässige Größe von 900 Byte überschreitet, wird eine Warnmeldung angezeigt.

Das Erstellen eines Indexes für eine berechnete Spalte kann bei einem Einfüge- oder Updatevorgang einen Fehler erzeugen, wenn der Einfüge- oder Updatevorgang zuvor funktioniert hat. Ein solcher Fehler tritt möglicherweise auf, wenn die berechnete Spalte einen arithmetischen Fehler zur Folge hat. In der folgenden Tabelle wird zum Beispiel die INSERT-Anweisung erfolgreich ausgeführt, obwohl die berechnete Spalte c einen arithmetischen Fehler zur Folge hat.

CREATE TABLE t1 (a INT, b INT, c AS a/b);
INSERT INTO t1 VALUES (1, 0);

Wenn Sie allerdings nach dem Erstellen der Tabelle einen Index für die berechnete Spalte c erstellen, meldet die gleiche INSERT-Anweisung einen Fehler.

CREATE TABLE t1 (a INT, b INT, c AS a/b);
CREATE UNIQUE CLUSTERED INDEX Idx1 ON t1(c);
INSERT INTO t1 VALUES (1, 0);

Weitere Informationen finden Sie unter Indexes on Computed Columns.

Eingeschlossene Spalten in Indizes

Nichtschlüsselspalten werden als eingeschlossene Spalten bezeichnet und können zur Blattebene eines nicht gruppierten Index hinzugefügt werden, um die Abfrageleistung durch Abdecken der Abfrage zu verbessern. Das heißt, alle Spalten, auf die in der Abfrage verwiesen wird, sind im Index als Schlüssel- oder Nichtschlüsselspalten enthalten. Dadurch kann der Abfrageoptimierer alle erforderlichen Informationen zu einem Indexscan suchen. Es erfolgt kein Zugriff auf die Daten der Tabelle oder des gruppierten Index. Weitere Informationen finden Sie unter Erstellen von Indizes mit eingeschlossenen Spalten und im Leitfaden zur Architektur und zum Design von SQL Server-Indizes.

Angeben von Indexoptionen

Mit SQL Server 2005 (9.x) wurden neue Indexoptionen eingeführt. Außerdem werden die Optionen auf andere Weise angegeben. In abwärtskompatibler Syntax entspricht WITH option_nameWITH (option_name = ON). Beim Festlegen von Indexoptionen gelten folgende Regeln:

• Neue Indexoptionen können nur mithilfe von WITH (<option_name> = <ON | OFF>) angegeben werden.
• Optionen können nicht sowohl mithilfe der abwärtskompatiblen als auch der neuen Syntax in derselben Anweisung angegeben werden. Wenn Sie beispielsweise WITH (DROP_EXISTING, ONLINE = ON) angeben, verursacht die Anweisung einen Fehler.
• Beim Erstellen eines XML-Index müssen die Optionen mithilfe von WITH (<option_name> = <ON | OFF>) angegeben werden.

DROP_EXISTING-Klausel

Mit der DROP_EXISTING-Klausel können Sie den Index neu erstellen, Spalten hinzufügen oder löschen, Optionen ändern, die Sortierreihenfolge für Spalten ändern sowie das Partitionsschema oder die Dateigruppe ändern.

Wenn der Index eine PRIMARY KEY- oder UNIQUE-Einschränkung erzwingt und die Indexdefinition in keiner Weise geändert wurde, wird der Index gelöscht und neu erstellt. Dabei wird die vorhandene Einschränkung beibehalten. Wenn die Indexdefinition jedoch geändert wird, schlägt die Anweisung fehl. Zum Ändern der Definition einer PRIMARY KEY- oder UNIQUE-Einschränkung müssen Sie die Einschränkung löschen und eine Einschränkung mit der neuen Definition hinzufügen.

Die DROP_EXISTING-Klausel erhöht die Leistung beim Neuerstellen eines gruppierten Index (mit der gleichen oder einer anderen Schlüsselmenge) für eine Tabelle, die auch nicht gruppierte Indizes besitzt. Die DROP_EXISTING-Klausel ersetzt die Ausführung einer DROP INDEX-Anweisung für den alten gruppierten Index mit anschließender Ausführung einer CREATE INDEX-Anweisung für den neuen gruppierten Index. Die nicht gruppierten Indizes werden einmal neu erstellt, dies aber nur dann, wenn die Indexdefinition geändert wurde. Die DROP_EXISTING-Klausel erstellt die nicht gruppierten Indizes nicht neu, wenn die Indexdefinition denselben Indexnamen, dieselben Schlüssel- und Partitionsspalten, dasselbe Eindeutigkeitsattribut und dieselbe Sortierreihenfolge wie der ursprüngliche Index aufweist.

Unabhängig davon, ob die nicht gruppierten Indizes neu erstellt werden, verbleiben sie immer in ihren ursprünglichen Dateigruppen oder Partitionsschemas und verwenden die ursprünglichen Partitionsfunktionen. Wenn ein gruppierter Index in einer anderen Dateigruppe oder einem anderen Partitionsschema neu erstellt wird, werden die nicht gruppierten Indizes nicht an den neuen Standort des gruppierten Index verschoben. Daher können auch die nicht gruppierten Indizes, die zuvor mit dem gruppierten Index ausgerichtet waren, möglicherweise nicht mehr damit ausgerichtet sein. Weitere Informationen zu Ausrichtung von partitionierten Indizes finden Sie unter Partitionierte Tabellen und Indizes.

Die DROP_EXISTING-Klausel sortiert die Daten nicht neu, wenn dieselben Indexschlüsselspalten in derselben Reihenfolge und mit derselben aufsteigenden oder absteigenden Reihenfolge verwendet werden, es sei denn, in der Indexanweisung ist ein nicht gruppierter Index angegeben und die ONLINE-Option ist auf OFF festgelegt. Wenn der gruppierte Index deaktiviert ist, muss der CREATE INDEX WITH DROP_EXISTING-Vorgang mit der ONLINE-Einstellung OFF durchgeführt werden. Wenn ein nicht gruppierter Index deaktiviert und keinem deaktivierten gruppierten Index zugeordnet ist, kann der CREATE INDEX WITH DROP_EXISTING-Vorgang mit der ONLINE-Einstellung OFF oder ON durchgeführt werden.

ONLINE (Option)

Die folgenden Regeln gelten für das Durchführen von Onlineindexvorgängen:

• Die zugrunde liegende Tabelle kann nicht geändert, abgeschnitten oder gelöscht werden, während ein Onlineindexvorgang verarbeitet wird.
• Beim Indexvorgang ist zusätzlicher temporärer Speicherplatz erforderlich.
• Onlinevorgänge können für partitionierte Indizes und Indizes durchgeführt werden, die persistente berechnete Spalten oder eingeschlossene Spalten enthalten.
• Mit der Argumentoption low_priority_lock_wait können Sie entscheiden, wie der Indexvorgang fortgesetzt werden kann, wenn er für die Sch-M-Sperre blockiert wird.

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

Ressourcen

Die folgenden Ressourcen sind für die Onlineerstellung des fortsetzbaren Indexes erforderlich:

• zusätzlichem Speicherplatz, damit der Index weiter erstellt wird, einschließlich der Zeit, wenn der Index angehalten wird
• Zusätzlicher Protokolldurchsatz während der Sortierungsphase Der insgesamt durch Protokolle belegte Speicherplatz für den fortsetzbaren Index ist geringer als bei der Erstellung eines regulären Onlineindexes. Zudem ist eine Protokollkürzung während dieses Vorgangs möglich.
• DDL-Status zur Verhinderung von DDL-Änderungen
• Ein Cleanup inaktiver Datensätze wird für den Index innerhalb des Builds für die Dauer des Vorgangs blockiert, während dieser angehalten und auch während der Vorgang ausgeführt wird.

Aktuelle funktionale Einschränkungen

Die folgenden Funktionen sind für Vorgänge zur Erstellung fortsetzbarer Indizes deaktiviert:

• Nachdem der Vorgang zum Erstellen eines fortsetzbaren Onlineindexes angehalten wurde, kann der ursprüngliche Wert von MAXDOP nicht mehr geändert werden.

• Die Erstellung eines Indexes, der Folgendes enthält:

  • Berechnete Spalte oder TIMESTAMP-Spalte als Schlüsselspalte
  • LOB-Spalte als enthaltene Spalte für die Erstellung des fortsetzbaren Indexes
  • Gefilterter Index

Fortsetzbare Indexvorgänge

Gilt für: SQL Server (ab SQL Server 2019 (15.x)) und Azure SQL-Datenbank

Die folgenden Richtlinien gelten für fortsetzbare Indexvorgänge:

• Eine Onlineneuerstellung eines Indexes wird mit der RESUMABLE = ON-Option als fortsetzbar angegeben.
• Die RESUMABLE-Option wird in den Metadaten nicht für einen bestimmten Index beibehalten und gilt nur für die Dauer der aktuellen DDL-Anweisung. Daher muss die RESUMABLE = ON-Klausel explizit angegeben werden, wenn Fortsetzbarkeit aktiviert werden soll.
• Die MAX_DURATION-Option wird nur für die RESUMABLE = ON-Option unterstützt.
• MAX_DURATION gibt bei der RESUMABLE-Option das Zeitintervall an, in dem ein Index erstellt wird. Sobald dieses Zeitintervall beendet ist, wird die Indexerstellung entweder angehalten oder in ihrer Ausführung beendet. Der Benutzer entscheidet, wann die Erstellung eines angehaltenen Index fortgesetzt werden kann. Die Zeitspanne in Minuten für MAX_DURATION muss größer als 0 Minuten und kleiner oder gleich einer Woche (7 × 24 × 60 = 10080 Minuten) sein. Das lange Anhalten eines Indexvorgangs kann Auswirkungen auf die DML-Leistung für eine bestimmte Tabelle sowie die Datenträgerkapazität der Datenbank haben, da sowohl der ursprüngliche Index als auch der neu erstellte Index Speicherplatz benötigen und während der DML-Vorgänge aktualisiert werden müssen. Wird die MAX_DURATION-Option ausgelassen, dann wird der Indexvorgang bis zum vollständigen Abschluss oder bis ein Fehler auftritt fortgeführt.
• Wenn Sie den Indexvorgang sofort anhalten möchten, können Sie den laufenden Befehl beenden (STRG+C) oder die Befehle ALTER INDEX PAUSE oder KILL <session_id> ausführen. Ein angehaltener Befehl kann mit dem Befehl ALTER INDEX fortgesetzt werden.
• Das erneute Ausführen der ursprünglichen CREATE INDEX-Anweisung für den fortsetzbaren Index führt dazu, dass ein angehaltener Indexvorgang automatisch fortgesetzt wird.
• Die Option SORT_IN_TEMPDB = ON wird für einen fortsetzbaren Index nicht unterstützt.
• Der DDL-Befehl mit RESUMABLE = ON kann nicht innerhalb einer expliziten Transaktion ausgeführt werden (kann nicht Teil des TRAN ... COMMIT-Blocks sein).
• Verwenden Sie die ALTER INDEX T-SQL-Syntax, um einen Index zu erstellen/neu zu erstellen/neu zu beenden.
• Deaktivierte Indizes werden nicht unterstützt.

Informationen zur Anzeige, dass eine Indexerstellung als fortsetzbarer Vorgang ausgeführt wird, und zur Überprüfung des aktuellen Ausführungsstatus finden Sie unter sys.index_resumable_operations.

WAIT_AT_LOW_PRIORITY bei Onlineindexvorgängen

Gilt für: Diese Syntax für CREATE INDEX gilt derzeit nur für SQL Server 2022 (16.x), Azure SQL-Datenbank und Azure SQL Managed Instance. Für ALTER INDEX gilt diese Syntax für SQL Server (ab SQL Server 2014 (12.x)) und Azure SQL-Datenbank. Weitere Informationen finden Sie unter ALTER INDEX.

Die Syntax low_priority_lock_wait ermöglicht die Angabe des Verhaltens WAIT_AT_LOW_PRIORITY. Die Verwendung von WAIT_AT_LOW_PRIORITY ist nur mit ONLINE=ON möglich.

Mit der WAIT_AT_LOW_PRIORITY-Option können Datenbankadministratoren die Sch-S- und Sch-M-Sperren, die für die Onlineerstellung von Indizes erforderlich sind, verwalten und eine von drei Optionen auswählen. In allen drei Fällen gilt: Sind während der Wartezeit (MAX_DURATION = n [minutes]) keine blockierenden Aktivitäten vorhanden, wird die Onlineindexneuerstellung ohne Wartezeit sofort ausgeführt, und die DDL-Anweisung wird abgeschlossen.

WAIT_AT_LOW_PRIORITY gibt an, dass der Onlineerstellungsvorgang für den Index auf Sperren mit niedriger Priorität wartet und die weitere Ausführung anderer Vorgänge ermöglicht, während der Onlineerstellungsvorgang für den Index wartet. 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 = time [MINUTES]

Die Wartezeit (ein ganzzahliger Wert in Minuten), während der die Sperren der Onlineindexerstellung mit niedriger Priorität warten, wenn der DDL-Befehl ausgeführt wird. Wenn der Vorgang während des MAX_DURATION-Zeitraums blockiert wird, wird die angegebene ABORT_AFTER_WAIT-Aktion ausgeführt. MAX_DURATION wird immer in Minuten angegeben, und das Wort MINUTES kann weggelassen werden.

ABORT_AFTER_WAIT = [NONE | SELF | BLOCKERS } ]

NONE Es wird weiterhin mit normaler (regulärer) Priorität auf die Sperre gewartet.

SELF beendet den DDL-Vorgang zur Onlineindexerstellung, der derzeit ausgeführt wird, ohne weitere Aktionen auszuführen. Die Option SELF kann nicht verwendet werden, wenn MAX_DURATION auf 0 (null) festgelegt ist.

BLOCKERS beendet alle Benutzertransaktionen, die den DDL-Vorgang zur Onlineindexneuerstellung blockieren, sodass der Vorgang fortgesetzt werden kann. Die BLOCKERS-Option erfordert, dass der Anmelder über ALTER ANY CONNECTION-Berechtigungen verfügt.

Zeilen- und Seitensperren (Optionen)

Wenn ALLOW_ROW_LOCKS = ON und ALLOW_PAGE_LOCK = ON angegeben sind, sind Sperren auf Zeilen-, Seiten- und Tabellenebene zulässig, wenn auf den Index zugegriffen wird. Das Datenbank-Engine wählt die geeignete Sperre aus und kann die Sperre von einer Zeilen- oder Seitensperre auf eine Tabellensperre ausweiten.

Wenn ALLOW_ROW_LOCKS = OFF und ALLOW_PAGE_LOCK = OFF angegeben sind, ist nur eine Sperre auf Tabellenebene zulässig, wenn auf den Index zugegriffen wird.

Sequenzielle Schlüssel

Gilt für: SQL Server (ab SQL Server 2019 (15.x)) und Azure SQL-Datenbank

Das Leistungsproblem von Speicherkonflikten bei Einfügevorgängen für die letzte Seite tritt häufig auf, wenn eine große Anzahl von gleichzeitigen Threads versucht, Zeilen in einen Index mit einem sequentiellen Schlüssel einzufügen. Ein Index wird als sequentiell betrachtet, wenn die führende Schlüsselspalte Werte enthält, die immer größer (oder kleiner) werden, wie beispielsweise eine Identitätsspalte oder ein Datum, das auf das aktuelle Datum/Uhrzeit voreingestellt ist. Da die einzufügenden Schlüssel sequentiell sind, werden alle neuen Zeilen am Ende der Indexstruktur eingefügt – also auf der gleichen Seite. Dies führt zu Konflikten für die Seite im Speicher, die als mehrere Threads betrachtet werden kann, die auf PAGELATCH_EX für die betreffende Seite warten.

Durch Aktivieren der Indexoption OPTIMIZE_FOR_SEQUENTIAL_KEY wird eine Optimierung in der Datenbank-Engine aktiviert, die den Durchsatz für Einfügevorgänge mit hoher Parallelität in den Index verbessert. Dies ist für Indizes vorgesehen, die einen sequenziellen Schlüssel enthalten und bei denen es daher verstärkt zu Speicherkonflikten bei Einfügevorgängen für die letzte Seite kommen kann, kann aber auch für Indizes verwendet werden, die Hotspots in anderen Bereichen des B-Strukturindex aufweisen.

Anzeigen von Indexinformationen

Informationen zu Indizes können Sie mithilfe von Katalogsichten, Systemfunktionen und gespeicherten Systemprozeduren zurückgeben.

Datenkomprimierung

Die Datenkomprimierung wird im Artikel Datenkomprimierung beschrieben. Die folgenden wichtigen Punkte sind zu berücksichtigen:

• Die Komprimierung kann es ermöglichen, mehr Zeilen auf einer Seite zu speichern, die maximale Zeilengröße wird dadurch allerdings nicht geändert.
• Nicht-Blattseiten eines Index sind nicht seitenkomprimiert, können jedoch zeilenkomprimiert sein.
• Jeder nicht gruppierte Index verfügt über eine eigene Komprimierungseinstellung und erbt die Komprimierungseinstellung nicht von der zugrunde liegenden Tabelle.
• Wenn ein gruppierter Index auf einem Heap erstellt wird, erbt der gruppierte Index den Komprimierungsstatus des Heaps, sofern kein anderer Komprimierungsstatus angegeben wird.

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

• Sie können die Komprimierungseinstellung einer einzelnen Partition nicht ändern, wenn die Tabelle nicht ausgerichtete Indizes aufweist.
• Mit der Syntax ALTER INDEX <index> ... REBUILD PARTITION ... wird die angegebene Partition des Index neu erstellt.
• Mit der Syntax ALTER INDEX <index> ... REBUILD WITH ... werden alle Partitionen des Index neu erstellt.

Mit der gespeicherten Prozedur sp_estimate_data_compression_savings können Sie einschätzen, wie sich eine Änderung des Komprimierungsstatus auf eine Tabelle, einen Index oder eine Partition auswirkt.

XML-Komprimierung

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

Viele der Überlegungen für die Datenkomprimierung gelten auch für die XML-Komprimierung. Beachten Sie auch die folgenden Aspekte:

• Bei Angabe einer Liste mit Partitionen kann die XML-Komprimierung für einzelne Partitionen aktiviert werden. Wenn die Liste der Partitionen nicht angegeben ist, wird für alle Partitionen die Verwendung der XML-Komprimierung festgelegt. Bei Erstellung einer Tabelle oder eines Indexes wird die XML-Datenkomprimierung deaktiviert, falls nicht anders angegeben. Bei Änderung einer Tabelle wird die vorhandene Komprimierung beibehalten, falls nicht anders angegeben.
• Wenn Sie eine Partitionsliste bzw. eine Partition außerhalb des zulässigen Bereichs angeben, wird ein Fehler generiert.
• Wenn ein gruppierter Index auf einem Heap erstellt wird, erbt der gruppierte Index den XML-Komprimierungsstatus des Heaps, sofern keine andere Komprimierungsoption angegeben wird.
• Zur Änderung der XML-Komprimierungseinstellung für einen Heap müssen alle nicht gruppierten Indizes der Tabelle neu erstellt werden, sodass sie auf die neuen Zeilenpositionen im Heap zeigen.
• Sie können die XML-Komprimierung online oder offline aktivieren und deaktivieren. Die Online-Aktivierung der Komprimierung für einen Heap erfolgt mit einem einzelnen Thread.
• Zur Ermittlung des XML-Komprimierungsstatus der Partitionen in einer partitionierten Tabelle fragen Sie die xml_compression-Spalte der sys.partitions-Katalogsicht ab.

Berechtigungen

Erfordert die ALTER-Berechtigung für die Sicht oder die Mitgliedschaft in der festen Datenbankrolle db_ddladmin.

Einschränkungen

In Azure Synapse Analytics und Analytics Platform System (PDW) können Sie Folgendes nicht erstellen:

• Einen gruppierten oder nicht gruppierten Rowstore-Index für eine Data Warehouse-Tabelle, wenn ein Columnstore-Index bereits vorhanden ist. Dieses Verhalten unterscheidet sich von SMP SQL Server, da dieser Dienst es zulässt, dass Rowstore- und Columnstore-Indizes beide in derselben vorhanden sind.
• Sie können keinen Index für eine Sicht erstellen.

Metadaten

Informationen zu vorhandenen Indizes erhalten Sie, wenn Sie die sys.indexes-Katalogsicht abfragen.

Versionshinweise

SQL-Datenbank unterstützt die Optionen „Dateigruppe“ und „Filestream“ nicht.

Beispiele: Alle Versionen. Verwendet die AdventureWorks-Datenbank.

A. Erstellen eines einfachen nicht gruppierten Rowstore-Index

Im folgenden Beispiel wird ein nicht gruppierter Index für die Spalte VendorID der Tabelle Purchasing.ProductVendor erstellt.

CREATE INDEX IX_VendorID ON ProductVendor (VendorID);
CREATE INDEX IX_VendorID ON dbo.ProductVendor (VendorID DESC, Name ASC, Address DESC);
CREATE INDEX IX_VendorID ON Purchasing..ProductVendor (VendorID);

B. Erstellen eines einfachen nicht gruppierten zusammengesetzten Rowstore-Index

Im folgenden Beispiel wird ein nicht gruppierter zusammengesetzter Index für die Spalten SalesQuota und SalesYTD der Tabelle Sales.SalesPerson erstellt.

CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD ON Sales.SalesPerson (SalesQuota, SalesYTD);

C. Erstellen eines Index für eine Tabelle in einer anderen Datenbank

Im folgenden Beispiel wird ein gruppierter Index für die VendorID-Spalte der ProductVendor-Tabelle in der Purchasing-Datenbank erstellt.

CREATE CLUSTERED INDEX IX_ProductVendor_VendorID ON Purchasing..ProductVendor (VendorID);

D: Hinzufügen einer Spalte zu einem Index

Im folgenden Beispiel wird der Index X_FF mit zwei Spalten der dbo.FactFinance-Tabelle erstellt. Die nächste Anweisung erstellt den Index mit zwei weiteren Spalten neu und behält den bereits vorhandenen Namen bei.

CREATE INDEX IX_FF ON dbo.FactFinance (FinanceKey ASC, DateKey ASC);

-- Rebuild and add the OrganizationKey
CREATE INDEX IX_FF ON dbo.FactFinance (FinanceKey, DateKey, OrganizationKey DESC)
  WITH (DROP_EXISTING = ON);

Beispiele: SQL Server, Azure SQL-Datenbank

E. Erstellen eines eindeutigen nicht gruppierten Index

Im folgenden Beispiel wird ein eindeutiger nicht gruppierter Index für die Name-Spalte der Production.UnitMeasure-Tabelle in der AdventureWorks2022-Datenbank erstellt. Der Index erzwingt Eindeutigkeit für die Daten, die in die Spalte Name eingefügt werden.

CREATE UNIQUE INDEX AK_UnitMeasure_Name
  ON Production.UnitMeasure(Name);

In der folgenden Abfrage wird die Eindeutigkeitseinschränkung getestet, indem eine Zeile mit demselben Wert wie in einer vorhandenen Zeile eingefügt wird.

-- Verify the existing value.
SELECT Name FROM Production.UnitMeasure WHERE Name = N'Ounces';
GO

INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name, ModifiedDate)
  VALUES ('OC', 'Ounces', GETDATE());

Die folgende Fehlermeldung wird angezeigt:

Server: Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'UnitMeasure' with unique index 'AK_UnitMeasure_Name'. The statement has been terminated.

F. Verwenden der IGNORE_DUP_KEY-Option

Das folgende Beispiel veranschaulicht die Wirkung der Option IGNORE_DUP_KEY, indem mehrere Zeilen zunächst mit dem Optionswert ON und anschließend mit dem Optionswert OFF in eine temporäre Tabelle eingefügt werden. Eine einzelne Zeile wird in die #Test-Tabelle eingefügt, die absichtlich einen doppelten Wert erzeugt, wenn die zweite mehrzeilige INSERT-Anweisung ausgeführt wird. Eine Zählung der Zeilen in der Tabelle gibt die Anzahl der eingefügten Zeilen zurück.

CREATE TABLE #Test (C1 NVARCHAR(10), C2 NVARCHAR(50), C3 DATETIME);
GO

CREATE UNIQUE INDEX AK_Index ON #Test (C2)
  WITH (IGNORE_DUP_KEY = ON);
GO

INSERT INTO #Test VALUES (N'OC', N'Ounces', GETDATE());
INSERT INTO #Test SELECT * FROM Production.UnitMeasure;
GO

SELECT COUNT(*) AS [Number of rows] FROM #Test;
GO

DROP TABLE #Test;
GO

Im Folgenden werden die Ergebnisse der zweiten INSERT-Anweisung aufgeführt.

Server: Msg 3604, Level 16, State 1, Line 5 Duplicate key was ignored.

Number of rows
--------------
38

Beachten Sie, dass die aus der Tabelle Production.UnitMeasure eingefügten Zeilen, die die Eindeutigkeitseinschränkung nicht verletzten, erfolgreich eingefügt wurden. Es wurde eine Warnung ausgegeben, und die doppelte Zeile wurde ignoriert, aber es wurde kein Rollback für die gesamte Transaktion ausgeführt.

Dieselben Anweisungen werden erneut ausgeführt. Dabei ist die Option IGNORE_DUP_KEY allerdings auf OFF festgelegt.

CREATE TABLE #Test (C1 NVARCHAR(10), C2 NVARCHAR(50), C3 DATETIME);
GO

CREATE UNIQUE INDEX AK_Index ON #Test (C2)
  WITH (IGNORE_DUP_KEY = OFF);
GO

INSERT INTO #Test VALUES (N'OC', N'Ounces', GETDATE());
INSERT INTO #Test SELECT * FROM Production.UnitMeasure;
GO

SELECT COUNT(*) AS [Number of rows] FROM #Test;
GO

DROP TABLE #Test;
GO

Im Folgenden werden die Ergebnisse der zweiten INSERT-Anweisung aufgeführt.

Server: Msg 2601, Level 14, State 1, Line 5
Cannot insert duplicate key row in object '#Test' with unique index
'AK_Index'. The statement has been terminated.

Number of rows
--------------
1

Beachten Sie, dass keine Zeilen aus der Production.UnitMeasure-Tabelle in die Tabelle eingefügt wurden, obwohl nur eine Zeile in der Tabelle die UNIQUE-Einschränkung für den Index verletzte.

G. Verwenden von DROP_EXISTING zum Löschen und Neuerstellen eines Index

Im folgenden Beispiel wird ein vorhandener Index für die ProductID-Spalte der Production.WorkOrder-Tabelle in der AdventureWorks2022-Datenbank mithilfe der DROP_EXISTING-Option gelöscht und neu erstellt. Die Optionen FILLFACTOR und PAD_INDEX sind ebenfalls festgelegt.

CREATE NONCLUSTERED INDEX IX_WorkOrder_ProductID
  ON Production.WorkOrder(ProductID)
    WITH (FILLFACTOR = 80,
      PAD_INDEX = ON,
      DROP_EXISTING = ON);
GO

H. Erstellen eines Index für eine Sicht

Im folgenden Beispiel werden eine Sicht und ein Index für diese Sicht erstellt. Dies beinhaltet zwei Abfragen, in denen die indizierte Sicht verwendet wird.

-- Set the options to support indexed views
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
  QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO

-- Create view with schemabinding
IF OBJECT_ID ('Sales.vOrders', 'view') IS NOT NULL
  DROP VIEW Sales.vOrders;
GO

CREATE VIEW Sales.vOrders
  WITH SCHEMABINDING
AS
  SELECT SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Revenue,
    OrderDate, ProductID, COUNT_BIG(*) AS COUNT
  FROM Sales.SalesOrderDetail AS od, Sales.SalesOrderHeader AS o
  WHERE od.SalesOrderID = o.SalesOrderID
  GROUP BY OrderDate, ProductID;
GO

-- Create an index on the view
CREATE UNIQUE CLUSTERED INDEX IDX_V1
  ON Sales.vOrders (OrderDate, ProductID);
GO

-- This query can use the indexed view even though the view is
-- not specified in the FROM clause.
SELECT SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Rev,
  OrderDate, ProductID
FROM Sales.SalesOrderDetail AS od
  JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID = o.SalesOrderID
    AND ProductID BETWEEN 700 AND 800
    AND OrderDate >= CONVERT(DATETIME, '05/01/2002', 101)
GROUP BY OrderDate, ProductID
ORDER BY Rev DESC;
GO

-- This query can use the above indexed view
SELECT OrderDate, SUM(UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS Rev
FROM Sales.SalesOrderDetail AS od
  JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID = o.SalesOrderID
    AND DATEPART(mm, OrderDate) = 3
  AND DATEPART(yy, OrderDate) = 2002
GROUP BY OrderDate
ORDER BY OrderDate ASC;
GO

I. Erstellen eines Index mit eingeschlossenen (Nichtschlüssel-)Spalten

Im folgenden Beispiel wird ein nicht gruppierter Index mit einer Schlüsselspalte (PostalCode) und vier Nichtschlüsselspalten (AddressLine1, AddressLine2, City, StateProvinceID) erstellt. Es folgt eine Abfrage, die vom Index abgedeckt wird. Wenn Sie den vom Abfrageoptimierer ausgewählten Index anzeigen möchten, wählen Sie in SQL Server Management Studio im Menü Abfrage die Option Display Actual Execution Plan (Tatsächlichen Ausführungsplan einschließen) aus.

CREATE NONCLUSTERED INDEX IX_Address_PostalCode
  ON Person.Address (PostalCode)
  INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);
GO

SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN N'98000' and N'99999';
GO

J. Erstellen eines partitionierten Index

Im folgenden Beispiel wird ein nicht gruppierter partitionierter Index für TransactionsPS1 (ein vorhandenes Partitionsschema in der AdventureWorks2022-Datenbank) erstellt. Dieses Beispiel setzt voraus, dass das Beispiel für einen partitionierten Index installiert wurde.

CREATE NONCLUSTERED INDEX IX_TransactionHistory_ReferenceOrderID
  ON Production.TransactionHistory (ReferenceOrderID)
  ON TransactionsPS1 (TransactionDate);
GO

K. Erstellen eines gefilterten Index

Im folgenden Beispiel wird ein gefilterter Index für die Production.BillOfMaterialsAdventureWorks2022-Tabelle in der -Datenbank erstellt. Das Filterprädikat kann Spalten einschließen, die keine Schlüsselspalten im gefilterten Index sind. Das Prädikat in diesem Beispiel wählt nur die Zeilen aus, in denen EndDate nicht NULL ist.

CREATE NONCLUSTERED INDEX "FIBillOfMaterialsWithEndDate"
  ON Production.BillOfMaterials (ComponentID, StartDate)
  WHERE EndDate IS NOT NULL;

L. Erstellen eines komprimierten Index

Im folgenden Beispiel wird ein Index für eine nicht partitionierte Tabelle unter Verwendung der Zeilenkomprimierung erstellt.

CREATE NONCLUSTERED INDEX IX_INDEX_1
  ON T1 (C2)
  WITH (DATA_COMPRESSION = ROW);
GO

Im folgenden Beispiel wird ein Index für eine partitionierte Tabelle unter Verwendung der Zeilenkomprimierung für alle Partitionen des Indexes erstellt.

CREATE CLUSTERED INDEX IX_PartTab2Col1
  ON PartitionTable1 (Col1)
  WITH (DATA_COMPRESSION = ROW);
GO

Im folgenden Beispiel wird ein Index für eine partitionierte Tabelle erstellt, wobei die Seitenkomprimierung für Partition 1 des Indexes und die Zeilenkomprimierung für die Partitionen 2 bis 4 des Indexes verwendet wird.

CREATE CLUSTERED INDEX IX_PartTab2Col1
  ON PartitionTable1 (Col1)
  WITH (
    DATA_COMPRESSION = PAGE ON PARTITIONS(1),
    DATA_COMPRESSION = ROW ON PARTITIONS (2 TO 4)
  );
GO

M. Erstellen eines Indexes mit XML-Komprimierung

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

Im folgenden Beispiel wird ein Index für eine nicht partitionierte Tabelle unter Verwendung der XML-Komprimierung erstellt. Mindestens eine Spalte im Index muss den XML-Datentyp aufweisen.

CREATE NONCLUSTERED INDEX IX_INDEX_1
  ON T1 (C2)
  WITH (XML_COMPRESSION = ON);
GO

Im folgenden Beispiel wird ein Index für eine partitionierte Tabelle unter Verwendung der XML-Komprimierung für alle Partitionen des Indexes erstellt.

CREATE CLUSTERED INDEX IX_PartTab2Col1
  ON PartitionTable1 (Col1)
  WITH (XML_COMPRESSION = ON);
GO

N. Erstellen, Fortsetzen, Anhalten und Abbrechen von Vorgängen fortsetzbarer Indizes

Gilt für: SQL Server (ab SQL Server 2019 (15.x)) und Azure SQL-Datenbank

-- Execute a resumable online index create statement with MAXDOP=1
CREATE INDEX test_idx1 ON test_table (col1) WITH (ONLINE = ON, MAXDOP = 1, RESUMABLE = ON);

-- Executing the same command again (see above) after an index operation was paused, resumes automatically the index create operation.

-- Execute a resumable online index creates operation with MAX_DURATION set to 240 minutes. After the time expires, the resumable index create operation is paused.
CREATE INDEX test_idx2 ON test_table (col2) WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 240);

-- Pause a running resumable online index creation
ALTER INDEX test_idx1 ON test_table PAUSE;
ALTER INDEX test_idx2 ON test_table PAUSE;

-- Resume a paused online index creation
ALTER INDEX test_idx1 ON test_table RESUME;
ALTER INDEX test_idx2 ON test_table RESUME;

-- Abort resumable index create operation which is running or paused
ALTER INDEX test_idx1 ON test_table ABORT;
ALTER INDEX test_idx2 ON test_table ABORT;

O. CREATE INDEX mit anderen Sperrzeitoptionen mit niedriger Priorität

In den folgenden Beispielen wird die WAIT_AT_LOW_PRIORITY-Option verwendet, um verschiedene Strategien für den Umgang mit Blockierungen anzugeben.

--Kill this session after waiting 5 minutes
CREATE CLUSTERED INDEX idx_1 ON dbo.T2 (a) WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = SELF)));
GO

--Kill blocker sessions
CREATE CLUSTERED INDEX idx_1 ON dbo.T2 (a) WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = BLOCKERS)));
GO

Im folgenden Beispiel wird die RESUMABLE-Option verwendet und es werden zwei MAX_DURATION-Werte angegeben, wobei der erste für die ABORT_AFTER_WAIT-Option und der zweite für die RESUMABLE-Option gilt.

--With resumable option; default locking behavior 
CREATE CLUSTERED INDEX idx_1 ON dbo.T2 (a) WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = NONE)), RESUMABLE = ON, MAX_DURATION = 240 MINUTES);

Beispiele: Azure Synapse Analytics und Analytics-Plattformsystem (PDW)

P. Grundlegende Syntax

Erstellen, Fortsetzen, Anhalten und Abbrechen von Vorgängen fortsetzbarer Indizes

Gilt für: SQL Server (ab SQL Server 2019 (15.x)) und Azure SQL-Datenbank

-- Execute a resumable online index create statement with MAXDOP=1
CREATE INDEX test_idx ON test_table WITH (ONLINE = ON, MAXDOP = 1, RESUMABLE = ON);

-- Executing the same command again (see above) after an index operation was paused, resumes automatically the index create operation.

-- Execute a resumable online index creates operation with MAX_DURATION set to 240 minutes. After the time expires, the resumable index create operation is paused.
CREATE INDEX test_idx ON test_table WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 240);

-- Pause a running resumable online index creation
ALTER INDEX test_idx ON test_table PAUSE;

-- Resume a paused online index creation
ALTER INDEX test_idx ON test_table RESUME;

-- Abort resumable index create operation which is running or paused
ALTER INDEX test_idx ON test_table ABORT;

Q. Erstellen eines nicht gruppierten Index für eine Tabelle in der aktuellen Datenbank

Im folgenden Beispiel wird ein nicht gruppierter Index für die Spalte VendorID der Tabelle ProductVendor erstellt.

CREATE INDEX IX_ProductVendor_VendorID
  ON ProductVendor (VendorID);

R. Erstellen eines nicht gruppierten Index für eine Tabelle in einer anderen Datenbank

Im folgenden Beispiel wird ein nicht gruppierter Index für die VendorID-Spalte der ProductVendor-Tabelle in der Purchasing-Datenbank erstellt.

CREATE CLUSTERED INDEX IX_ProductVendor_VendorID
  ON Purchasing..ProductVendor (VendorID);

S. Erstellen eines geordneten gruppierten Index für eine Tabelle

Im folgenden Beispiel wird ein geordneter gruppierter Index für die c1-Spalte und die c2-Spalte der T1-Tabelle in der MyDB-Datenbank erstellt.

CREATE CLUSTERED COLUMNSTORE INDEX MyOrderedCCI ON MyDB.dbo.T1 
ORDER (c1, c2);

T. Umwandeln eines gruppierten Columnstore-Index in einen geordneten gruppierten Index für eine Tabelle

Im folgenden Beispiel wird der vorhandene gruppierte Columnstore-Index in einen geordneten gruppierten Columnstore-Index mit dem Namen MyOrderedCCI für die Spalten c1 und c2 der Tabelle T2 in der Datenbank MyDB umgewandelt.

CREATE CLUSTERED COLUMNSTORE INDEX MyOrderedCCI ON MyDB.dbo.T2
ORDER (c1, c2)
WITH (DROP_EXISTING = ON);

Weitere Informationen

• Leitfaden zur Architektur und zum Entwurf von SQL Server-Indizes
• Ausführen von Onlineindexvorgängen
• Indizes und ALTER TABLE
• ALTER INDEX
• CREATE PARTITION FUNCTION
• CREATE PARTITION SCHEME
• CREATE SPATIAL INDEX
• CREATE STATISTICS
• CREATE TABLE
• CREATE XML INDEX
• Datentypen
• DBCC SHOW_STATISTICS
• DROP INDEX
• XML-Indizes (SQL Server)
• sys.indexes
• sys.index_columns
• sys.xml_indexes
• EVENTDATA