CREATE INDEX (Transact-SQL)

  • Artikel

Erstellt einen relationalen Index für die angegebene Tabelle oder Sicht einer angegebenen Tabelle. Ein Index kann erstellt werden, bevor Daten in der Tabelle enthalten sind. Relationale Indizes können durch Angabe eines gekennzeichneten Datenbanknamens für Tabellen oder Sichten einer anderen Datenbank erstellt werden.

HinweisHinweis

Informationen zum Erstellen eines XML-Index finden Sie unter CREATE XML INDEX (Transact-SQL). Informationen zum Erstellen eines räumlichen Index finden Sie unter CREATE SPATIAL INDEX (Transact-SQL). Informationen zum Erstellen eines speicheroptimierten xVelocity-columnstore-Indexes finden Sie unter CREATE COLUMNSTORE INDEX (Transact-SQL).

Themenlink (Symbol) Transact-SQL-Syntaxkonventionen

Syntax

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 ] . | schema_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 }
  | DROP_EXISTING = { ON | OFF }
  | ONLINE = { ON | OFF }
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE} 
     [ ON PARTITIONS ( { <partition_number_expression> | <range> } 
     [ , ...n ] ) ]
}

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

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

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

<comparison> ::= 
        column_name <comparison_op> constant 

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

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


Backward Compatible Relational Index 
Important   The backward compatible relational index syntax structure will be removed in a future version of SQL Server. Avoid using this syntax structure in new development work, and plan to modify applications that currently use the feature. Use the syntax structure specified in <relational_index_option> instead.

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 
}

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.

    Database Engine (Datenbankmodul) 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 Database Engine (Datenbankmodul) 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.

    Ist CLUSTERED nicht angegeben, wird ein nicht gruppierter Index erstellt.

    HinweisHinweis

    Da die Blattebene eines gruppierten Index und seine Datenseiten per Definition identisch sind, bewirkt das Erstellen eines gruppierten Index und das Verwenden der ON partition_scheme_name- oder ON filegroup_name-Klausel effektiv, dass eine Tabelle aus der Dateigruppe, in der die Tabelle erstellt wurde, in das neue Partitionsschema oder die neue Dateigruppe verschoben wird. Bevor Sie Tabellen oder Indizes für bestimmte Dateigruppen erstellen, überprüfen Sie, welche Dateigruppen verfügbar sind, und stellen Sie sicher, dass in ihnen ausreichend freier Speicherplatz für den Index vorhanden ist.

    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.

    Der Standardwert ist NONCLUSTERED.

  • 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 Spalte(n) an, auf der bzw. 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 16 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 Werte des zusammengesetzten Index beträgt 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 mit den 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. Der Standardwert 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.

    Die 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 Erstellen von Indizes mit eingeschlossenen Spalten.

    Mit Ausnahme von text, ntext und image sind alle Datentypen zulässig. Der Index muss offline erstellt oder neu erstellt werden (ONLINE = OFF), wenn eine der angegebenen Nichtschlüsselspalten den Datentyp 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. Bei berechneten Spalten, die auf der Grundlage der Datentypen image, ntext, text, varchar(max), nvarchar(max), varbinary(max) und xml erstellt wurden, kann es sich um eingeschlossene Nichtschlüsselspalten handeln, vorausgesetzt, die berechneten Spaltendatentypen sind als eingeschlossene Spalten zulässig. Weitere Informationen finden Sie unter Indizes in berechneten Spalten.

    Weitere Informationen zum Erstellen eines XML-Index finden Sie unter CREATE XML INDEX (Transact-SQL).

  • 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 den IS NULL-Operator und den IS NOT NULL-Operator.

    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 Partitionierungsfunktion übereinstimmen, die partition_scheme_name verwendet. column_name ist nicht auf 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 Database Engine (Datenbankmodul) die Eindeutigkeit der Schlüsselwerte in nur einer einzigen Partition überprüfen.

    HinweisHinweis

    Beim Partitionieren eines nicht eindeutigen gruppierten Index fügt Database Engine (Datenbankmodul) standardmäßig die Partitionierungsspalte zu der Liste der gruppierten Indexschlüssel hinzu, sofern sie dort noch nicht angegeben wurde. Beim Partitionieren eines nicht eindeutigen, nicht gruppierten Indexes fügt Database Engine (Datenbankmodul) die Partitionierungsspalte als (eingeschlossene) Nichtschlüsselspalte des Indexes hinzu, sofern sie noch nicht angegeben wurde.

    Wenn partition_scheme_name oder filegroup bei einer partitionierten Tabelle nicht angegeben werden, wird der Index in demselben Partitionsschema platziert und verwendet dieselbe Partitionsspalte wie die zugrunde liegende Tabelle.

    HinweisHinweis

    Sie können kein Partitionierungsschema für einen XML-Index angeben. Beim Partitionieren der Basistabelle verwendet der XML-Index dasselbe Partitionsschema wie die Tabelle.

    Weitere Informationen zum Partitionieren 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 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 für die Standarddateigruppe.

    Der Begriff default ist in diesem Kontext kein Schlüsselwort. Er ist ein Bezeichner für die Standarddateigruppe und muss begrenzt sein, wie in ON "default" oder ON [default]. Wenn "default" angegeben ist, 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 (Transact-SQL).

  • [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]
    Gibt die Platzierung der FILESTREAM-Daten für die Tabelle an, wann 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>::=

Gibt das vollqualifizierte oder nicht vollqualifizierte Objekt an, das indiziert werden soll.

  • database_name
    Gibt den Namen der Datenbank an.

  • schema_name
    Gibt den Namen des Schemas an, zu dem die Tabelle oder Sicht gehört.

  • table_or_view_name
    Gibt den Namen der zu indizierenden Tabelle oder Sicht an.

    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 in dem Abschnitt zu Hinweisen.

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

    • ON
      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 Database Engine (Datenbankmodul) 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 ist WITH PAD_INDEX gleichwertig mit WITH PAD_INDEX = ON.

  • FILLFACTOR **=**fillfactor
    Gibt einen Prozentsatz an, der angibt, wie weit Database Engine (Datenbankmodul) die Blattebene jeder Indexseite während der Indexerstellung oder -neuerstellung füllen soll. fillfactor muss ein ganzzahliger Wert zwischen 1 und 100 sein. Wenn fillfactor 100 ist, erstellt das Database Engine (Datenbankmodul) Indizes mit vollständig aufgefüllten Blattseiten.

    Die FILLFACTOR-Einstellung gilt nur, wenn der Index erstellt oder neu erstellt wird. Database Engine (Datenbankmodul) hält den angegebenen Prozentsatz des Speicherplatzes nicht dynamisch auf den Seiten frei. Zum Anzeigen der Füllfaktoreinstellung verwenden Sie die Katalogsicht sys.indexes.

    Wichtiger HinweisWichtig

    Das Erstellen eines gruppierten Indexes mit einem FILLFACTOR-Wert unter 100 wirkt sich auf den Speicherplatz aus, den die Daten belegen, da Database Engine (Datenbankmodul) die Daten beim Erstellen des gruppierten Indexes neu verteilt.

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

    • ON
      Die zum Erstellen des Indexes verwendeten Zwischenergebnisse des Sortierens werden in tempdb gespeichert. Dadurch kann sich die zum Erstellen eines Index erforderliche Zeit verringern, wenn sich tempdb auf einem anderen Datenträgersatz befindet als die Benutzerdatenbank. Sie erhöht jedoch den Betrag an Speicherplatz, der während der Indexerstellung verwendet wird.

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

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

    In abwärtskompatibler Syntax ist WITH SORT_IN_TEMPDB gleichwertig mit WITH SORT_IN_TEMPDB = ON.

  • IGNORE_DUP_KEY = { ON | OFF }
    Gibt die Fehlerantwort auf den Versuch eines Einfügevorgangs an, 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. Die Standardeinstellung ist OFF.

    • ON
      Eine Warnmeldung wird ausgegeben, wenn doppelte Schlüsselwerte in einen eindeutigen Index eingefügt werden. Es schlagen nur die Zeilen fehl, die gegen die Eindeutigkeitseinschränkung verstoßen.

    • OFF
      Eine Fehlermeldung wird ausgegeben, wenn doppelte Schlüsselwerte in einen eindeutigen Index eingefügt werden. Für den gesamten INSERT-Vorgang wird ein Rollback ausgeführt.

    IGNORE_DUP_KEY kann für Indizes, die für eine Sicht erstellt werden, nicht eindeutige Indizes, XML-Indizes, räumliche und gefilterte Indizes nicht auf ON festgelegt werden.

    Um IGNORE_DUP_KEY anzuzeigen, verwenden Sie sys.indexes.

    In abwärtskompatibler Syntax ist WITH IGNORE_DUP_KEY gleichwertig mit WITH IGNORE_DUP_KEY = ON.

  • STATISTICS_NORECOMPUTE = { ON | OFF}
    Gibt an, ob Verteilungsstatistiken neu berechnet werden. Der Standardwert ist OFF.

    • ON
      Veraltete Indexstatistiken werden nicht automatisch neu berechnet.

    • OFF
      Die automatischen Updates der Statistiken sind aktiviert.

    Um das automatische Aktualisieren von Statistiken wiederherzustellen, müssen Sie STATISTICS_NORECOMPUTE auf OFF festlegen oder die UPDATE STATISTICS-Anweisung ohne die NORECOMPUTE-Klausel ausführen.

    Wichtiger HinweisWichtig

    Wenn Sie die automatische Neuberechnung von Verteilungsstatistiken deaktivieren, wählt der Abfrageoptimierer möglicherweise nicht die optimalen Ausführungspläne für Abfragen, an denen die Tabelle beteiligt ist.

    In abwärtskompatibler Syntax ist WITH STATISTICS_NORECOMPUTE gleichwertig mit WITH STATISTICS_NORECOMPUTE = ON.

  • DROP_EXISTING = { ON | OFF }
    Gibt an, dass der benannte, bereits vorhandene gruppierte oder nicht gruppierte Index gelöscht und neu erstellt wird. Der Standardwert ist OFF.

    • ON
      Der vorhandene Index wird gelöscht und neu erstellt. Der angegebene Indexname muss mit dem eines derzeit vorhandenen Indexes übereinstimmen. Die Indexdefinition kann jedoch geändert werden. Sie können beispielsweise andere Spalten, eine andere Sortierreihenfolge, ein anderes Partitionsschema oder andere Indexoptionen angeben.

    • OFF
      Es wird ein Fehler angezeigt, wenn der angegebene Indexname bereits vorhanden ist.

    Der Indextyp kann nicht mithilfe von DROP_EXISTING geändert werden.

    In abwärtskompatibler Syntax ist WITH DROP_EXISTING gleichwertig mit WITH DROP_EXISTING = ON.

  • ONLINE = { ON | OFF }
    Gibt an, ob zugrunde liegende Tabellen oder zugehörige Indizes für Abfragen und Datenänderungen während des Indexvorgangs zur Verfügung stehen. Die Standardeinstellung ist OFF.

    HinweisHinweis

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

    • 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 (IS) 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 die Quelle für kurze Zeit eine freigegebene Sperre (S) aktiviert, wenn ein nicht gruppierter Index erstellt wird. Eine Schemaänderungssperre (SCH-M) wird aktiviert, wenn ein gruppierter Index online erstellt oder gelöscht wird, und wenn ein gruppierter oder nicht gruppierter Index neu erstellt wird. ONLINE kann nicht auf ON festgelegt werden, wenn ein Index 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 Funktionsweise von Onlineindexvorgängen.

    Indizes, einschließlich Indizes für globale temporäre Tabellen, können mit den folgenden Ausnahmen online erstellt werden:

    • XML-Index

    • Index für eine lokale temporäre Tabelle.

    • Eindeutiger gruppierter Anfangsindex für eine Sicht.

    • Deaktivierte gruppierte Indizes.

    • Gruppierter Index, sofern die zugrunde liegende Tabelle LOB-Datentypen enthält: image, ntext, text und räumliche Typen.

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

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

    • ON
      Zeilensperren sind beim Zugriff auf den Index zulässig. Die Database Engine (Datenbankmodul) bestimmt, wann Zeilensperren verwendet werden.

    • OFF
      Zeilensperren werden nicht verwendet.

  • ALLOW_PAGE_LOCKS = { ON | OFF }
    Gibt an, ob Seitensperren zulässig sind. Die Standardeinstellung ist ON.

    • ON
      Seitensperren sind beim Zugriff auf den Index zulässig. Database Engine (Datenbankmodul) bestimmt, wann Seitensperren verwendet werden.

    • OFF
      Seitensperren werden nicht verwendet.

  • MAXDOP = max_degree_of_parallelism
    Überschreibt die Konfigurationsoption Konfigurieren der Serverkonfigurationsoption Max. Grad an Parallelität für die Dauer des Indexvorgangs. Mit MAXDOP können Sie die Anzahl der Prozessoren begrenzen, die bei der Ausführung paralleler Pläne verwendet werden. Maximal sind 64 Prozessoren zulässig.

    Mögliche Werte für max_degree_of_parallelism sind:

    • 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 (Standardwert)
      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.

    HinweisHinweis

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

  • DATA_COMPRESSION
    Gibt die Datenkomprimierungsoption für den angegebenen Index, die Partitionsnummer oder den Bereich von Partitionen an. Folgende Optionen stehen zur Verfügung:

    • NONE
      Der Index oder die angegebenen Partitionen werden nicht komprimiert.

    • ROW
      Der Index oder die angegebenen Partitionen werden mit Zeilenkomprimierung komprimiert.

    • PAGE
      Der Index oder die angegebenen Partitionen werden mit Seitenkomprimierung komprimiert.

    Weitere Informationen zur Komprimierung finden Sie unter Datenkomprimierung.

  • ON PARTITIONS ( { <partition_number_expression> | <range> } [ ,...n ] )
    Gibt die Partitionen an, für die die DATA_COMPRESSION-Einstellung gilt. Wenn der Index nicht partitioniert ist, erzeugt das ON PARTITIONS-Argument einen Fehler. Wenn die ON PARTITIONS-Klausel nicht angegeben wird, gilt die DATA_COMPRESSION-Option für alle Partitionen eines partitionierten Index.

    <partition_number_expression> kann auf die folgenden Weisen angegeben werden:

    • Geben Sie die Nummer der Partition an, beispielsweise: ON PARTITIONS (2).

    • Geben Sie die Partitionsnummern mehrerer einzelner Partitionen durch Trennzeichen getrennt an, beispielsweise: ON PARTITIONS (1, 5).

    • Geben Sie sowohl Bereiche als auch einzelne Partitionen an, beispielsweise: ON PARTITIONS (2, 4, 6 TO 8).

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

    Wenn Sie für verschiedene Partitionen unterschiedliche Datenkomprimierungstypen festlegen möchten, geben Sie die Option DATA_COMPRESSION mehrmals an, 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)
)

Hinweise

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 Indexerstellungsvorgang kann minimal protokolliert werden, wenn das Wiederherstellungsmodell der Datenbank auf die massenprotokollierte oder einfache Wiederherstellung 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.

Durch Indizes werden erweiterte Eigenschaften 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.

Eindeutige Indizes

Wenn ein eindeutiger Index vorhanden ist, prüft Database Engine (Datenbankmodul) 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 Database Engine (Datenbankmodul) 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.

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

VorsichtshinweisVorsicht

Das Erstellen bzw. Neuerstellen von nicht ausgerichteten Indizes für eine Tabelle mit mehr als 1.000 Partitionen ist möglich, wird aber nicht unterstützt. Dies hätte Leistungseinbußen oder eine zu hohe Speicherauslastung während der Vorgänge zur Folge. Es empfiehlt sich, bei mehr als 1.000 Partitionen nur ausgerichtete Indizes zu verwenden.

Beim Partitionieren eines nicht eindeutigen gruppierten Index fügt Database Engine (Datenbankmodul) 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 partitionierten Indizes finden Sie unter Partitionierte Tabellen und Indizes.

In SQL Server 2012 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.

Gefilterten 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 Required Value 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 Abfrageoptimierer verwendet den gefilterten Index des Abfrageausführungsplans.

    SET-Optionen

    Erforderlicher Wert

    ANSI_NULLS

    ON

    ANSI_PADDING

    ON

    ANSI_WARNINGS*

    ON

    ARITHABORT

    ON

    CONCAT_NULL_YIELDS_NULL

    ON

    NUMERIC_ROUNDABORT

    OFF

    QUOTED_IDENTIFIER

    ON

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

  • Database Engine (Datenbankmodul) 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 den Index des Abfrageausführungsplans von Transact-SQL-Anweisungen nicht.

Weitere Informationen zu gefilterten Indizes finden Sie unter Erstellen gefilterter Indizes.

Räumliche Indizes

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

XML-Indizes

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

Indexschlüsselgröße

Die maximal zulässige Größe für einen Indexschlüssel beträgt 900 Byte. Indizes für Spalten des Datentyps varchar mit einer Größe von über 900 Bytes können erstellt werden, wenn die in den Spalten vorhandenen Daten zum Zeitpunkt der Indexerstellung die Größe von 900 Bytes nicht überschreiten. Allerdings schlagen nachfolgende Einfüge- oder Updateaktionen für die Spalten fehl, durch die die Gesamtgröße von 900 Bytes überschritten wird. Der Indexschlüssel eines gruppierten Indexes 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 Database Engine (Datenbankmodul) beim Berechnen der Indexschlüsselgröße nicht berücksichtigt. Weitere Informationen finden Sie unter Erstellen von Indizes mit eingeschlossenen Spalten.

HinweisHinweis

Falls beim Partitionieren der Tabellen die Partitionierungsschlüsselspalten nicht bereits in einem nicht eindeutigen gruppierten Index vorhanden sind, werden Sie mithilfe von Database Engine (Datenbankmodul) dem Index hinzugefügt. Die kombinierte Größe der indizierten Spalten (eingeschlossene Spalten werden nicht gezählt), zzgl. beliebiger hinzugefügter Partitionierungsspalten dürfen 1800 Byte in einem nicht eindeutigen gruppierten Index nicht übersteigen.

Berechnete Spalten

Indizes können für berechnete Spalten erstellt werden. Zudem können berechnete Spalten die Eigenschaft PERSISTED besitzen. Dies bedeutet, dass Database Engine (Datenbankmodul) die berechneten Werte in der Tabelle speichert und diese aktualisiert, wenn eine andere Spalte aktualisiert wird, von der die berechnete Spalte abhängt. In Database Engine (Datenbankmodul) werden diese persistenten Werte verwendet, 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 basierte 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 Database Engine (Datenbankmodul) deterministisch, aber unpräzise sind.

Für persistente berechnete Spalten müssen die folgenden SET-Optionen wie im vorherigen Abschnitt zu den erforderlichen SET-Optionen für indizierte Sichten dargestellt 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 zum Determinismus finden Sie unter Deterministische und nicht deterministische Funktionen.

Berechnete Spalten, die auf der Grundlage der Datentypen image, ntext, text, varchar(max), nvarchar(max), varbinary(max) und xml erstellt wurden, können als Schlüsselspalten oder als eingeschlossene Nichtschlüsselspalten indiziert werden, vorausgesetzt, der Datentyp der berechneten Spalte ist als Indexschlüsselspalte oder Nichtschlüsselspalte zulässig. 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 Indizes in berechneten Spalten.

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 über einen 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.

Angeben von Indexoptionen

Mit SQL Server 2005 wurden neue Indexoptionen eingeführt. Außerdem werden die Optionen auf andere Weise angegeben. In der abwärtskompatiblen Syntax ist WITH option_name gleichbedeutend mit WITH ( <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 mithilfe der abwärtskompatiblen und der neuen Syntax in derselben Anweisung angegeben werden. Wenn Sie beispielsweise WITH (DROP_EXISTING, ONLINE = ON**)** angeben, schlägt die Anweisung fehl.

  • 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 zur partitionierten Indexausrichtung finden Sie unter .

Die DROP_EXISTING-Klausel sortiert die Daten nicht erneut, 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 ist 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.

Wenn Indizes mit 128 oder mehr Blöcken gelöscht oder neu erstellt werden, verzögert Database Engine (Datenbankmodul) die eigentlichen Seitenzuordnungsaufhebungen und die zugehörigen Sperren bis zu einem Zeitpunkt nach dem Transaktionscommit.

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, wenn 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.

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

Zeilen- und Seitensperren (Optionen)

Wenn ALLOW_ROW_LOCKS = ON und ALLOW_PAGE_LOCK = ON ist, sind beim Zugreifen auf den Index Sperren auf Zeilen-, Seiten- und Tabellenebene zulässig. Das Database Engine (Datenbankmodul) 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 ist, ist beim Zugreifen auf den Index nur eine Sperre auf Tabellenebene zulässig.

Anzeigen von Indexinformationen

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

Datenkomprimierung

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

  • Die Komprimierung kann es ermöglichen, dass mehr Zeilen auf einer Seite gespeichert werden, die maximale Zeilengröße wird durch sie allerdings nicht geändert.

  • Nicht-Blattseiten eines Index sind nicht seitenkomprimiert, können jedoch zeilenkomprimiert sein.

  • Jeder nicht gruppierte Index verfügt über eine eigenen Komprimierungseinstellung und erbt die Komprimierungseinstellung nicht von der zugrunde liegenden Tabelle.

  • Wenn ein gruppierter Index auf dem Heap erstellt wird, erbt der gruppierte Index den Komprimirierungsstatus vom Heap, 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 blockfreie Indizes aufweist.

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

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

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

Berechtigungen

Erfordert die ALTER-Berechtigung auf der Tabelle oder Sicht. Der Benutzer muss ein Mitglied der festen Serverrolle sysadmin bzw. der festen Datenbankrollen db_ddladmin und db_owner sein.

Beispiele

A.Erstellen eines einfachen nicht gruppierten Index

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

USE AdventureWorks2012;
GO
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IX_ProductVendor_VendorID')
    DROP INDEX IX_ProductVendor_VendorID ON Purchasing.ProductVendor;
GO
CREATE INDEX IX_ProductVendor_VendorID 
    ON Purchasing.ProductVendor (BusinessEntityID); 
GO

B.Erstellen eines einfachen nicht gruppierten zusammengesetzten Index

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

USE AdventureWorks2012
GO
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IX_SalesPerson_SalesQuota_SalesYTD')
    DROP INDEX IX_SalesPerson_SalesQuota_SalesYTD ON Sales.SalesPerson ;
GO
CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD
    ON Sales.SalesPerson (SalesQuota, SalesYTD);
GO

C.Erstellen eines eindeutigen nicht gruppierten Index

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

USE AdventureWorks2012;
GO
IF EXISTS (SELECT name from sys.indexes
             WHERE name = N'AK_UnitMeasure_Name')
    DROP INDEX AK_UnitMeasure_Name ON Production.UnitMeasure;
GO
CREATE UNIQUE INDEX AK_UnitMeasure_Name 
    ON Production.UnitMeasure(Name);
GO

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.

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

USE AdventureWorks2012;
GO
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.

USE AdentureWorks2012;
GO
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.

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

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

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

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

USE AdventureWorks2012;
GO
--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

G.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 Tatsächlichen Ausführungsplan einschließen aus.

USE AdventureWorks2012;
GO
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IX_Address_PostalCode')
    DROP INDEX IX_Address_PostalCode ON Person.Address;
GO
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

H.Erstellen eines partitionierten Index

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

USE AdentureWorks2012;
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'IX_TransactionHistory_ReferenceOrderID'
    AND object_id = OBJECT_ID(N'Production.TransactionHistory'))
DROP INDEX IX_TransactionHistory_ReferenceOrderID
    ON Production.TransactionHistory;
GO
CREATE NONCLUSTERED INDEX IX_TransactionHistory_ReferenceOrderID
    ON Production.TransactionHistory (ReferenceOrderID)
    ON TransactionsPS1 (TransactionDate);
GO

I.Erstellen eines gefilterten Index

Im folgenden Beispiel wird ein gefilterter Index für die Tabelle "Production.BillOfMaterials" 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.

USE AdventureWorks2012;
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsWithEndDate' 
    AND object_id = OBJECT_ID(N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials;
GO
CREATE NONCLUSTERED INDEX "FIBillOfMaterialsWithEndDate"
    ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL;
GO

J.Erstellen eines komprimierten Index

Im folgenden Beispiel wird ein Index für eine nicht partitionierte Tabelle mit 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 mit 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

Siehe auch

Verweis

ALTER INDEX (Transact-SQL)

CREATE PARTITION FUNCTION (Transact-SQL)

CREATE PARTITION SCHEME (Transact-SQL)

CREATE SPATIAL INDEX (Transact-SQL)

CREATE STATISTICS (Transact-SQL)

CREATE TABLE (Transact-SQL)

CREATE XML INDEX (Transact-SQL)

Datentypen (Transact-SQL)

DBCC SHOW_STATISTICS (Transact-SQL)

DROP INDEX (Transact-SQL)

sys.indexes (Transact-SQL)

sys.index_columns (Transact-SQL)

sys.xml_indexes (Transact-SQL)

EVENTDATA (Transact-SQL)

Konzepte

XML-Indizes (SQL Server)