CREATE INDEX (Transact-SQL)

  • Artikel

Aktualisiert: 14. April 2006

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

Themenlink (Symbol)Transact-SQL-Syntaxkonventionen

Syntax

Create Relational Index CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name 
    ON <object> ( column [ ASC | DESC ] [ ,...n ] ) 
    [ INCLUDE ( column_name [ ,...n ] ) ]
    [ WITH ( <relational_index_option> [ ,...n ] ) ]
    [ ON { partition_scheme_name ( column_name ) 
         | filegroup_name 
         | default 
         }
    ]
[ ; ]

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

Create XML Index CREATE [ PRIMARY ] XML INDEX index_name 
    ON <object> ( xml_column_name )
    [ USING XML INDEX xml_index_name 
        [ FOR { VALUE | PATH | PROPERTY } ] ]
    [ WITH ( <xml_index_option> [ ,...n ] ) ]
[ ; ]

<object> ::=
{
    [ database_name. [ schema_name ] . | schema_name. ] 
        table_name
}

<xml_index_option> ::=
{ 
    PAD_INDEX  = { ON | OFF }
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | DROP_EXISTING = { ON | OFF }
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
}

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.

    SQL Server 2005-Datenbankmodul lässt das Erstellen eines eindeutigen Indexes 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 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 Indexes als Duplikate angesehen werden.

  • CLUSTERED
    Erstellt einen Index, in dem die logische Reihenfolge der Schlüsselwerte die physikalische Reihenfolge der entsprechenden Zeilen in einer Tabelle bestimmt. Die unterste Ebene oder Blattebene des gruppierten Indexes enthält die tatsächlichen Datenzeilen der Tabelle. Ein Tabelle oder Sicht kann immer nur einen gruppierten Index haben. Weitere Informationen finden Sie unter Gruppierte Indexstrukturen.

    Eine Sicht mit einem eindeutigen gruppierten Index wird als indizierte Sicht bezeichnet. Durch das Erstellen eines eindeutigen gruppierten Indexes für eine Sicht wird die Sicht physikalisch 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 Entwerfen 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.

    ms188783.note(de-de,SQL.90).gifHinweis:
    Da die Blattebene eines gruppierten Indexes und seine Datenseiten per Definition identisch sind, bewirkt das Erstellen eines gruppierten Indexes 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. Weitere Informationen finden Sie unter Ermitteln der Speicherplatzanforderungen für Indizes.

  • NONCLUSTERED
    Erstellt einen Index, der die logische Reihenfolge einer Tabelle angibt. Bei einem nicht gruppierten Index ist die physikalische Reihenfolge der Datenzeilen unabhängig von deren indizierter Reihenfolge. Weitere Informationen finden Sie unter Strukturen nicht gruppierter Indizes.

    Jede Tabelle kann bis zu 249 nicht gruppierte Indizes haben, 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 Indexes. 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.

    Primäre XML-Indexnamen dürfen nicht mit den folgenden Zeichen beginnen: #, ##, @ oder @@.

  • 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 Indexes beträgt 900 Byte. Weitere Informationen zu Spalten mit variabler Breite in zusammengesetzten Indizes finden Sie im Abschnitt zu den Hinweisen.

    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 Indexes hinzugefügt werden sollen. Der nicht gruppierte Index kann eindeutig oder nicht eindeutig sein.

    Die maximale Anzahl der enthaltenen Nichtschlüsselspalten beträgt 1.023 Spalten, die minimale Anzahl beträgt 1 Spalte.

    Die Spaltennamen können in der INCLUDE-Liste nicht wiederholt und können nicht gleichzeitig als Schlüssel- und Nichtschlüsselspalten verwendet werden. Weitere Informationen finden Sie unter Index 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 Erstellen von Indizes für berechnete Spalten.

  • ON partition_scheme_name**(column_name)**
    Gibt das Partitionsschema an, das die Dateigruppen definiert, denen die Partitionen eines partitionierten Indexes zugeordnet werden. Das Partitionsschema muss in der Datenbank vorhanden sein. Dazu können Sie entweder CREATE PARTITION SCHEME oder ALTER PARTITION SCHEME ausführen. column_name gibt die Spalte an, für die der partitionierte Index partitioniert werden soll. Diese Spalte muss mit dem Datentyp, der Länge und der Genauigkeit des Arguments der Partitionsfunktion übereinstimmen, die von partition_scheme_name verwendet wird. 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 Datenbankmodul die Eindeutigkeit der Schlüsselwerte in nur einer einzigen Partition überprüfen.

    ms188783.note(de-de,SQL.90).gifHinweis:
    Beim Partitionieren eines nicht eindeutigen gruppierten Indexes fügt 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 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.

    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 Spezielle Richtlinien für partitionierte 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. XML-Indizes verwenden dieselbe Dateigruppe wie die Tabelle.

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

  • [PRIMARY] XML
    Erstellt einen XML-Index für die angegebene xml-Spalte. Wenn PRIMARY angegeben ist, wird ein gruppierter Index mit dem gruppierten Schlüssel erstellt, der aus dem Gruppierungsschlüssel der Benutzertabelle und dem Bezeichner für einen XML-Knoten besteht. Jede Tabelle kann bis zu 249 XML-Indizes enthalten. Beachten Sie beim Erstellen eines XML-Indexes Folgendes:

    • Für den Primärschlüssel der Benutzertabelle muss ein gruppierter Index vorhanden sein.
    • Der Gruppierungsschlüssel der Benutzertabelle ist auf 15 Spalten begrenzt.
    • Für jede xml-Spalte in einer Tabelle können ein primärer XML-Index und mehrere sekundäre XML-Indizes vorhanden sein.
    • Für eine xml-Spalte muss ein primärer XML-Index vorhanden sein, bevor ein sekundärer XML-Index für die Spalte erstellt werden kann.
    • Ein XML-Index kann nur für eine einzige xml-Spalte erstellt werden. Sie können keinen XML-Index für eine Nicht-xml-Spalte erstellen. Außerdem können Sie keinen relationalen Index für eine xml-Spalte erstellen.
    • Sie können weder einen primären noch einen sekundären XML-Index für eine xml-Spalte in einer Sicht, für eine Tabellenwertvariable mit xml-Spalten oder für Variablen des Typs xml erstellen.
    • Sie können keinen primären XML-Index für eine berechnete xml-Spalte erstellen.
    • Die SET-Optionseinstellungen müssen mit den Einstellungen übereinstimmen, die für indizierte Sichten und berechnete Spaltenindizes erforderlich sind. Insbesondere muss die Option ARITHABORT auf ON festgelegt sein, wenn ein XML-Index erstellt und Werte in der xml-Spalte eingefügt, gelöscht oder aktualisiert werden. Weitere Informationen finden Sie unter SET-Optionen mit Auswirkungen auf Ergebnisse.

    Weitere Informationen finden Sie unter Indizes für Spalten vom xml-Datentyp.

  • xml_column_name
    Gibt die xml-Spalte an, auf der der Index basiert. Für eine einzige XML-Indexdefinition kann nur eine xml-Spalte angegeben werden. Allerdings können für eine xml-Spalte mehrere sekundäre XML-Indizes erstellt werden.
  • USING XML INDEX xml_index_name
    Gibt den primären XML-Index an, der beim Erstellen eines sekundären XML-Indexes verwendet werden soll.

  • FOR { VALUE | PATH | PROPERTY }
    Gibt den Typ des sekundären XML-Indexes an.

    • VALUE
      Erstellt einen sekundären XML-Index für Spalten, bei denen die Schlüsselspalten (Knotenwert und Pfad) vom primären XML-Index stammen.
    • PATH
      Erstellt einen sekundären XML-Index für Spalten, die auf Pfad- und Knotenwerten im primären XML-Index basieren. Im sekundären Index von PATH handelt es sich bei den Pfad- und Knotenwerten um Schlüsselspalten, die ein effizientes Suchen nach Pfaden ermöglichen.
    • PROPERTY
      Erstellt einen sekundären XML-Index für Spalten (PS, Pfad und Knotenwert) des primären XML-Indexes. Dabei steht PS für den Primärschlüssel der Basistabelle.
<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 das Auffüllen 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, setzt Datenbankmodul diesen Prozentsatz intern außer Kraft, 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 Prozentwert an, der dem Füllfaktor entspricht. Dieser Faktor legt fest, wie weit Datenbankmodul die Blattebene jeder Indexseite während der Indexerstellung oder -neuerstellung auffüllen soll. fillfactor muss ein ganzzahliger Wert zwischen 1 und 100 sein. Der Standardwert lautet 0. Wenn fillfactor 100 oder 0 entspricht, werden von Datenbankmodul Indizes mit vollständig aufgefüllten Blattseiten erstellt.

    ms188783.note(de-de,SQL.90).gifHinweis:
    Die Füllfaktorwerte 0 und 100 sind in jeder Hinsicht identisch.

    Die FILLFACTOR-Einstellung gilt nur, wenn der Index erstellt oder neu erstellt wird. 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.

    ms188783.note(de-de,SQL.90).gifWichtig:
    Das Erstellen eines gruppierten Indexes mit einem FILLFACTOR-Wert unter 100 wirkt sich auf den Speicherplatz aus, den die Daten belegen, da Datenbankmodul die Daten beim Erstellen des gruppierten Indexes neu verteilt.

    Weitere Informationen finden Sie unter Füllfaktor.

  • 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 Indexes erforderliche Zeit verringern, wenn sich tempdb auf einem anderen Datenträgersatz befindet als die Benutzerdatenbank. Allerdings wird dadurch die Menge an Speicherplatz erhöht, die während der Indexerstellung verwendet wird.
    • OFF
      Die Zwischenergebnisse des Sortierens 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 tempdb und Indexerstellung.

    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 doppelte Schlüsselwerte beim Einfügen mehrerer Zeilen für einen eindeutigen gruppierten oder einen eindeutigen nicht gruppierten Index an. Der Standardwert ist OFF.

    • ON
      Es wird eine Warnmeldung ausgegeben, und nur die Zeilen schlagen fehl, die den eindeutigen Index verletzen.
    • OFF
      Es wird eine Warnmeldung ausgegeben, und für die gesamte INSERT-Transaktion wird ein Rollback ausgeführt.

    Die Einstellung IGNORE_DUP_KEY gilt nur für Einfügevorgänge, die nach dem Erstellen oder Neuerstellen des Indexes auftreten. Diese Einstellung hat keine Auswirkungen auf den Vorgang der Indexerstellung.

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

    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 Statistiken werden nicht automatisch neu berechnet.
    • OFF
      Das automatische Aktualisieren von Statistiken ist aktiviert.

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

    ms188783.note(de-de,SQL.90).gifWichtig:
    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, nicht gruppierte oder XML-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, relational oder XML, kann nicht mithilfe von DROP_EXISTING geändert werden. Es ist auch nicht möglich, einen primären XML-Index als sekundären XML-Index neu zu definieren oder umgekehrt.

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

  • ONLINE = { ON | OFF }
    Gibt an, ob zugrunde liegende Tabellen und dazugehörige Indizes beim Indexvorgang für Abfragen und Datenänderungen verfügbar sind. Der Standardwert ist OFF.

    ms188783.note(de-de,SQL.90).gifHinweis:
    Onlineindexvorgänge sind nur in SQL Server 2005 Enterprise Edition verfügbar.
    • ON
      Langzeittabellensperren werden nicht für die Dauer des Indexvorgangs aufrechterhalten. Während der Hauptphase des Indexvorgangs wird nur eine beabsichtigte gemeinsame Sperre (IS) für die Quelltabelle aufrechterhalten. Dadurch können Abfragen oder Aktualisierungen für die zugrunde liegende Tabelle und die Indizes fortgesetzt werden. Zu Beginn des Vorgangs wird für sehr kurze Zeit eine gemeinsame Sperre (S) für das Quellobjekt aufrechterhalten. Am Ende des Vorgangs wird für die Quelle für kurze Zeit eine gemeinsame 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 gemeinsame Sperre (S) für die Tabelle. Dadurch werden Aktualisierungen der zugrunde liegenden Tabelle verhindert. Lesevorgänge, wie SELECT-Anweisungen, sind jedoch zulässig.

    Weitere Informationen finden Sie unter Funktionsweise von Onlineindexvorgängen. Weitere Informationen zu Sperren finden Sie unter Sperrmodi.

    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, varchar(max), nvarchar(max), varbinary(max) und xml.
    • Nicht gruppierter Index, der mit Spalten des LOB-Datentyps definiert ist.
      ms188783.note(de-de,SQL.90).gifHinweis:
      Ein nicht eindeutiger nicht gruppierter Index kann online erstellt werden, wenn die Tabelle LOB-Datentypen enthält. Dabei darf jedoch keine dieser Spalten in der Indexdefinition als Schlüssel- oder (eingeschlossene) Nichtschlüsselspalte verwendet werden.

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

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

    • ON
      Zeilensperren sind beim Zugreifen auf den Index zulässig. In Datenbankmodul ist festgelegt, wann Zeilensperren verwendet werden.
    • OFF
      Es werden keine Zeilensperren verwendet.

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

    • ON
      Seitensperren sind beim Zugreifen auf den Index zulässig. In Datenbankmodul ist festgelegt, wann Seitensperren verwendet werden.
    • OFF
      Es werden keine Seitensperren verwendet.

  • MAXDOP = max_degree_of_parallelism
    Setzt die Konfigurationsoption Max. Grad an Parallelität für die Dauer des Indexvorgangs außer Kraft. 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 je nach aktueller Systemauslastung die tatsächliche Anzahl der Prozessoren oder weniger.

    Weitere Informationen finden Sie unter Konfigurieren von Parallelindexvorgänge.

    ms188783.note(de-de,SQL.90).gifHinweis:
    Parallele Indexvorgänge sind nur in SQL Server 2005 Enterprise Edition verfügbar.

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 mit SQL Server 2005 Enterprise Edition kann CREATE INDEX mehr Prozessoren verwenden, um die mit dem Erstellen des Indexes verknüpften Scan- und Sortiervorgänge auszuführen. Dies geschieht in gleicher Weise wie für andere Abfragen. Weitere Informationen finden Sie unter Konfigurieren von Parallelindexvorgänge.

Der Indexerstellungsvorgang kann minimal protokolliert werden, wenn das Wiederherstellungsmodell der Datenbank auf massenprotokolliert oder auf einfach festgelegt ist. Weitere Informationen finden Sie unter Auswählen eines Wiederherstellungsmodells für Indexvorgänge.

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. Weitere Informationen finden Sie unter Verwenden von erweiterten Eigenschaften für Datenbankobjekte.

Gruppierte Indizes

Für das Erstellen eines gruppierten Indexes für eine Tabelle (Heap) oder das Löschen und Neuerstellen eines vorhandenen gruppierten Indexes 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 finden Sie unter Ermitteln der Speicherplatzanforderungen für Indizes. Weitere Informationen zu gruppierten Indizes finden Sie unter Erstellen von gruppierten Indizes.

Eindeutige Indizes

Wenn ein eindeutiger Index vorhanden ist, prüft 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 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. Weitere Informationen zu eindeutigen Indizes finden Sie unter Erstellen eindeutiger Indizes.

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.

Beim Partitionieren eines nicht eindeutigen gruppierten Indexes fügt 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.

Indizierte Sichten

Wird ein eindeutiger gruppierter Index für eine Sicht erstellt, so wird die Abfrageleistung verbessert, da die Sicht genauso wie eine Tabelle mit einem gruppierten Index in der Datenbank gespeichert wird. Der Abfrageoptimierer kann indizierte Sichten verwenden, um die Abfrageausführung zu beschleunigen. Es ist nicht erforderlich, dass in der Abfrage auf die jeweilige Sicht verwiesen wird, damit der Optimierer diese Sicht für eine Ersetzung berücksichtigt.

Die folgenden Schritte sind zum Erstellen einer indizierten Sicht erforderlich und wichtig für eine erfolgreiche Implementierung der Sicht:

  1. Stellen Sie sicher, dass die SET-Optionen für alle vorhandenen Tabellen richtig sind, auf die in der Sicht verwiesen wird.
  2. Stellen Sie sicher, dass die SET-Optionen für die Sitzung richtig festgelegt sind, bevor Sie neue Tabellen und die Sicht erstellen.
  3. Stellen Sie sicher, dass die Sichtdefinition deterministisch ist.
  4. Erstellen Sie die Sicht mithilfe der Option WITH SCHEMABINDING.
  5. Erstellen Sie den eindeutigen gruppierten Index für die Sicht.

Erforderliche SET-Optionen für indizierte Sichten

Das Auswerten desselben Ausdrucks kann in Datenbankmodul zu unterschiedlichen Ergebnissen führen, wenn bei der Ausführung der Abfrage unterschiedliche SET-Optionen aktiviert sind. Wenn die SET-Option CONCAT_NULL_YIELDS_NULL auf ON festgelegt ist, gibt beispielsweise der Ausdruck 'abc' + NULL den Wert NULL zurück. Wenn die Option CONCAT_NULL_YIEDS_NULL allerdings auf OFF festgelegt ist, ergibt derselbe Ausdruck 'abc'.

Um sicherzustellen, dass die Sichten ordnungsgemäß verwaltet werden können und konsistente Ergebnisse zurückgeben, sind für indizierte Sichten feste Werte für mehrere SET-Optionen erforderlich. Die SET-Optionen in der folgenden Tabelle müssen auf die in der Spalte ErforderlicherWert angezeigten Werte festgelegt werden, wenn eine der folgenden Bedingungen zutrifft:

  • Die indizierte Sicht wird erstellt.

  • Für eine Tabelle, die Teil der indizierten Sicht ist, wird ein Einfüge-, Aktualisierungs- oder Löschvorgang durchgeführt. Dazu gehören Vorgänge wie Massenkopieren, Replikation und verteilte Abfragen.

  • Die indizierte Sicht 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

    ON

    ON

    ON

    OFF

    ANSI_PADDING

    ON

    ON

    ON

    OFF

    ANSI_WARNINGS*

    ON

    ON

    ON

    OFF

    ARITHABORT

    ON

    ON

    OFF

    OFF

    CONCAT_NULL_YIELDS_NULL

    ON

    ON

    ON

    OFF

    NUMERIC_ROUNDABORT

    OFF

    OFF

    OFF

    OFF

    QUOTED_IDENTIFIER

    ON

    ON

    ON

    OFF

    *In SQL Server 2005 wird durch Festlegen von ANSI_WARNINGS auf ON implizit ARITHABORT auf ON festgelegt, wenn der Datenbank-Kompatibilitätsgrad 90 beträgt. Wenn der Datenbank-Kompatibilitätsgrad kleiner oder gleich 80 ist, muss die Option ARITHABORT explizit auf ON festgelegt werden.

Wenn Sie eine OLE DB- oder ODBC-Serververbindung verwenden, müssen Sie nur den Wert der ARITHABORT-Einstellung ändern. Alle DB-Library-Werte müssen entweder auf Serverebene mithilfe von sp_configure oder über die Anwendung mithilfe des SET-Befehls ordnungsgemäß festgelegt werden. Weitere Informationen zu SET-Optionen finden Sie unter Verwenden von Optionen in SQL Server.

ms188783.note(de-de,SQL.90).gifWichtig:
Es wird dringend empfohlen, die Benutzeroption ARITHABORT serverweit auf ON festzulegen, sobald in einer Datenbank auf dem Server die erste indizierte Sicht oder der erste Index für eine berechnete Spalte erstellt wird.

Deterministische Funktionen

Die Definition einer indizierten Sicht muss deterministisch sein. Eine Sicht ist deterministisch, wenn alle Ausdrücke in der Auswahlliste sowie die WHERE- und GROUP BY-Klauseln deterministisch sind. Deterministische Ausdrücke geben stets dasselbe Ergebnis zurück, wenn sie mit einer bestimmten Gruppe von Eingabewerten ausgewertet werden. Nur deterministische Funktionen können Teil von deterministischen Ausdrücken sein. Beispielsweise ist die DATEADD-Funktion deterministisch, weil sie für eine bestimmte Gruppe von Argumentwerten stets dasselbe Ergebnis für die drei Parameter zurückgibt. GETDATE ist nicht deterministisch, weil diese Funktion immer mit demselben Argument aufgerufen wird, aber der zurückgegebene Wert ist bei jeder Ausführung unterschiedlich. Weitere Informationen finden Sie unter Deterministische und nicht deterministische Funktionen.

Auch wenn ein Ausdruck deterministisch ist, kann das exakte Ergebnis von der Prozessorarchitektur oder der Version des Microcodes abhängen, wenn dieser Ausdruck float-Ausdrücke enthält. Um die Datenintegrität sicherzustellen, können solche Ausdrücke nur als Nichtschlüsselspalten von indizierten Sichten verwendet werden. Deterministische Ausdrücke, die keine float-Ausdrücke enthalten, werden als präzise bezeichnet. Nur präzise deterministische Ausdrücke können in indizierten Sichten Teile von Schlüsselspalten und von WHERE- oder GROUP BY-Klauseln sein.

Mithilfe der IsDeterministic-Eigenschaft der COLUMNPROPERTY-Funktion können Sie bestimmen, ob eine Sichtspalte deterministisch ist. Mithilfe der IsPrecise-Eigenschaft der COLUMNPROPERTY-Funktion können Sie bestimmen, ob eine deterministische Spalte in einer Sicht mit Schemabindung präzise ist. COLUMNPROPERTY gibt den Wert 1 für TRUE, den Wert 0 für FALSE und NULL für ungültige Eingaben zurück. Dies bedeutet, dass die Spalte nicht deterministisch oder nicht präzise ist.

Zusätzliche Anforderungen

Zusätzlich zu den Anforderungen für SET-Optionen und deterministische Funktionen müssen die folgenden Anforderungen erfüllt werden:

  • Der Benutzer, der die CREATE INDEX-Anweisung ausführt, muss der Besitzer der Sicht sein.

  • Wenn die Sichtdefinition eine GROUP BY-Klausel enthält, kann der Schlüssel des eindeutigen gruppierten Indexes nur auf die Spalten verweisen, die in der GROUP BY-Klausel angegeben sind.

  • Für die Basistabellen müssen bei der Tabellenerstellung die richtigen SET-Optionen festgelegt worden sein, sonst kann die Sicht mit Schemabindung nicht darauf verweisen.

  • Auf Tabellen muss in der Sichtdefinition mit zweiteiligen Namen verwiesen werden: schema**.**tablename.

  • Benutzerdefinierte Funktionen müssen mithilfe der Option WITH SCHEMABINDING erstellt werden.

  • Auf benutzerdefinierte Funktionen muss mit zweiteiligen Namen verwiesen werden: schema**.**function.

  • Die Sicht muss mithilfe der Option WITH SCHEMABINDING erstellt werden.

  • Die Sicht darf nur auf Basistabellen in derselben Datenbank verweisen, nicht auf andere Sichten.

  • Die Sichtdefinition darf Folgendes nicht enthalten:

    COUNT(*)

    ROWSET-Funktion

    Abgeleitete Tabelle

    Selbstverknüpfung

    DISTINCT

    STDEV, VARIANCE, AVG

    Spalten mit dem Datentyp float*, text, ntext oder image

    Unterabfrage

    Volltextprädikate (CONTAIN, FREETEXT)

    SUM für einen Ausdruck, der NULL zulässt

    CLR-benutzerdefinierte Aggregatfunktion

    TOP

    MIN, MAX

    UNION

    *Die indizierte Sicht kann Spalten mit dem Datentyp float enthalten. Allerdings dürfen solche Spalten nicht im gruppierten Indexschlüssel enthalten sein.

Wenn GROUP BY vorhanden ist, muss die VIEW-Definition COUNT_BIG(*) enthalten, während HAVING nicht enthalten sein darf. Diese GROUP BY-Einschränkungen gelten nur für die indizierte Sichtdefinition. Im Ausführungsplan einer Abfrage kann eine indizierte Sicht auch dann verwendet werden, wenn sie diese GROUP BY-Einschränkungen nicht erfüllt.

Indizierte Sichten können für eine partitionierte Tabelle erstellt werden und können selbst partitioniert werden. Weitere Informationen zum Partitionieren finden Sie im vorherigen Abschnitt zu partitionierten Indizes.

Wenn Sie verhindern möchten, dass Datenbankmodul indizierte Sichten verwendet, schließen Sie den OPTION (EXPAND VIEWS)-Hinweis in die Abfrage ein. Außerdem kann der Optimierer die Indizes für die Sichten nicht verwenden, wenn eine der aufgeführten Optionen falsch festgelegt ist. Weitere Informationen zum OPTION (EXPAND VIEWS)-Hinweis finden Sie unter SELECT (Transact-SQL).

Der Kompatibilitätsgrad der Datenbank kann nicht kleiner als 80 sein. Für eine Datenbank, die eine indizierte Sicht enthält, kann der Kompatibilitätsgrad nicht auf einen Wert geändert werden, der kleiner als 80 ist.

XML-Indizes

Weitere Informationen finden Sie unter Indizes für Spalten vom xml-Datentyp.

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 Byte können erstellt werden, wenn die in den Spalten vorhandenen Daten zum Zeitpunkt der Indexerstellung die Größe von 900 Byte nicht überschreiben. Allerdings schlagen nachfolgende Einfüge- oder Aktualisierungsaktionen für die Spalten fehl, durch die die Gesamtgröße von 900 Byte überschritten wird. Weitere Informationen finden Sie unter Maximale Größe von Indexschlüsseln. Der Indexschlüssel eines gruppierten Indexes kann keine Spalten des Datentyps varchar enthalten, bei denen Daten in der Zuordnungseinheit ROW_OVERFLOW_DATA vorhanden sind. Wenn ein gruppierter Index für eine Spalte des Datentyps varchar erstellt wird und Daten in der Zuordnungseinheit IN_ROW_DATA vorhanden sind, schlagen nachfolgende Einfüge- oder Aktualisierungsaktionen für die Spalten fehl, durch die Daten durch Ausführen eines Pushs außerhalb von Zeilen verschoben werden. Weitere Informationen zu Zuordnungseinheiten finden Sie unter Organisationsstruktur von Tabellen und Indizes.

In SQL Server 2005 können nicht gruppierte Indizes Nichtschlüsselspalten auf der Blattebene des Indexes enthalten. Diese Spalten werden von Datenbankmodul beim Berechnen der Indexschlüsselgröße nicht berücksichtigt. Weitere Informationen finden Sie unter Index mit eingeschlossenen Spalten.

Berechnete Spalten

Indizes können für berechnete Spalten erstellt werden. In SQL Server 2005 können berechnete Spalten die Eigenschaft PERSISTED besitzen. Dies bedeutet, dass 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 Datenbankmodul werden diese permanenten 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 Datenbankmodul deterministisch, aber unpräzise sind.

Für permanente 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 permanent 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 Aktualisierungsvorgang einen Fehler erzeugen, wenn der Einfüge- oder Aktualisierungsvorgang 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 Erstellen von Indizes für berechnete Spalten.

Eingeschlossene Spalten in Indizes

Nichtschlüsselspalten werden als eingeschlossene Spalten bezeichnet und können zur Blattebene eines nicht gruppierten Indexes 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 Indexes. Weitere Informationen finden Sie unter Index mit eingeschlossenen Spalten.

Angeben von Indexoptionen

Mit SQL Server 2005 werden 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-Indexes 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 Indexes (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 Indexes 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 Spezielle Richtlinien für partitionierte Indizes.

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 Datenbankmodul die eigentlichen Seitenzuordnungsaufhebungen und die zugehörigen Sperren bis zu einem Zeitpunkt nach dem Transaktionscommit. Weitere Informationen finden Sie unter Löschen und Neuerstellen großer Objekte.

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. Weitere Informationen finden Sie unter Ermitteln der Speicherplatzanforderungen für Indizes.
  • Onlinevorgänge können für partitionierte Indizes und Indizes durchgeführt werden, die permanente 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. Datenbankmodul wählt die geeignete Sperre aus und kann die Sperre von einer Zeilen- oder Seitensperre auf eine Tabellensperre ausweiten. Weitere Informationen finden Sie unter Sperrenausweitung (Datenbankmodul).

Wenn ALLOW_ROW_LOCKS = OFF und ALLOW_PAGE_LOCK = OFF ist, ist beim Zugreifen auf den Index nur eine Sperre auf Tabellenebene zulässig.

Weitere Informationen zum Konfigurieren der Sperrgranularität für einen Index finden Sie unter Anpassen der Sperren für einen Index.

Anzeigen von Indexinformationen

Informationen zu Indizes können Sie mithilfe von Katalogsichten, Systemfunktionen und gespeicherten Systemprozeduren zurückgeben. Weitere Informationen finden Sie unter Anzeigen von Indexinformationen.

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 Indexes

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

USE AdventureWorks;
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 (VendorID); 
GO

B. Erstellen eines einfachen nicht gruppierten zusammengesetzten Indexes

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

USE AdventureWorks
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 Indexes

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 AdventureWorks;
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 AdventureWorks;
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 wurden erneut ausgeführt. Dabei war die Option IGNORE_DUP_KEY allerdings auf OFF festgelegt.

USE AdventureWorks;
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 Indexes

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 AdventureWorks;
GO
CREATE NONCLUSTERED INDEX IX_WorkOrder_ProductID
    ON Production.WorkOrder(ProductID)
    WITH (FILLFACTOR = 80,
        PAD_INDEX = ON,
        DROP_EXISTING = ON);
GO

F. Erstellen eines Indexes 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 AdventureWorks;
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 Indexes 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 AdventureWorks;
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 primären XML-Indexes

Im folgenden Beispiel wird ein primärer XML-Index für die Spalte CatalogDescription in der Tabelle Production.ProductModel erstellt.

USE AdventureWorks;
GO
IF EXISTS (SELECT * FROM sys.indexes
            WHERE name = N'PXML_ProductModel_CatalogDescription')
    DROP INDEX PXML_ProductModel_CatalogDescription 
        ON Production.ProductModel;
GO
CREATE PRIMARY XML INDEX PXML_ProductModel_CatalogDescription
    ON Production.ProductModel (CatalogDescription);
GO

I. Erstellen eines sekundären XML-Indexes

Im folgenden Beispiel wird ein sekundärer XML-Index für die Spalte CatalogDescription in der Tabelle Production.ProductModel erstellt.

USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IXML_ProductModel_CatalogDescription_Path')
    DROP INDEX IXML_ProductModel_CatalogDescription_Path
        ON Production.ProductModel;
GO
CREATE XML INDEX IXML_ProductModel_CatalogDescription_Path 
    ON Production.ProductModel (CatalogDescription)
    USING XML INDEX PXML_ProductModel_CatalogDescription FOR PATH ;
GO

J. Erstellen eines partitionierten Indexes

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. Weitere Informationen zur Installation finden Sie unter Readme_PartitioningScript.

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

Siehe auch

Verweis

ALTER INDEX (Transact-SQL)
CREATE PARTITION FUNCTION (Transact-SQL)
CREATE PARTITION SCHEME (Transact-SQL)
CREATE STATISTICS (Transact-SQL)
CREATE TABLE (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)

Andere Ressourcen

Ermitteln der Speicherplatzanforderungen für Indizes
Allgemeine Richtlinien zum Indexentwurf
Indizes für Spalten vom xml-Datentyp
Tabellen- und Indexarchitektur

Hilfe und Informationen

Informationsquellen für SQL Server 2005

Änderungsverlauf

Version Verlauf

14. April 2006
Aktualisierter Inhalt:
  • Eindeutige nicht gruppierte Indizes wurden aus der Liste der nicht online erstellbaren Indizes entfernt. Diese Änderung gilt für SQL Server 2005 Service Pack 1 und höher.
  • Der Tabelle der SET-Optionen wurde eine Fußnote zur Auswirkung auf die ARITHABORT-Einstellung beim Festlegen von ANSI_WARNINGS auf ON hinzugefügt.

05. Dezember 2005
Neuer Inhalt:
  • Der Liste der nicht online erstellbaren Indizes wurden eindeutige nicht gruppierte Indizes hinzugefügt.