Freigeben über

  • Artikel

CREATE INDEX (Transact-SQL)

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

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 ] ) ]
    [ 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 Index 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 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 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.

    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. 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 physische Reihenfolge der Datenzeilen unabhängig von deren indizierter Reihenfolge. Weitere Informationen finden Sie unter Strukturen nicht gruppierter Indizes.

    Jede Tabelle kann bis zu 999 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 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 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.

    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 wird eine einfache Vergleichslogik verwendet. Es darf darin nicht auf eine berechnete Spalte, eine UDT-Spalte, eine Spalte mit einem räumlichen Datentyp oder eine Spalte mit dem hierarchyID-Datentyp verwiesen werden. Vergleiche mit NULL-Literalen sind bei 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 > '20040101' AND EndDate <= '20040630'

    WHERE ComponentID IN (533, 324, 753)

    WHERE StartDate IN ('20040404', '20040905') 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 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 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 Index fügt Database Engine (Datenbankmodul) die Partitionierungsspalte als (eingeschlossene) Nichtschlüsselspalte des Index 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 Erstellen eines XML-Index finden Sie unter CREATE XML INDEX (Transact-SQL).

    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.

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

    Eine Liste der FILESTREAM-Themen finden Sie unter Entwerfen und Implementieren der FILESTREAM-Speicherung.

<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 Index verwendet werden sollen.

  • PAD_INDEX = { ON | OFF }
    Gibt die Leerstellen in 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 Database Engine (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 das Database Engine (Datenbankmodul) die Blattebene jeder Indexseite während der Indexerstellung oder -neuerstellung auffü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 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 Index 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 Index 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 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 Aktualisierungen 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 und dazugehörige Indizes beim Indexvorgang für Abfragen und Datenänderungen verfügbar sind. Der Standardwert ist OFF.

    HinweisHinweis

    Onlineindexvorgänge sind nur in der SQL Server Enterprise, Developer und Evaluation Edition verfügbar.

    • ON
      Während des Indexvorgangs werden keine Langzeitsperren für Tabellen 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 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 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.

      HinweisHinweis

      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. 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. Die Database Engine (Datenbankmodul) bestimmt, wann Seitensperren verwendet werden.

    • OFF
      Seitensperren werden nicht verwendet.

  • MAXDOP = max_degree_of_parallelism
    Setzt die Konfigurationsoption max degree of parallelism 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 die tatsächliche Anzahl oder, je nach aktueller Systemauslastung, eine geringere Anzahl von Prozessoren

    Weitere Informationen finden Sie unter Konfigurieren von Parallelindexvorgänge.

    HinweisHinweis

    Parallele Indexvorgänge sind nur in den Editionen SQL Server Enterprise, Developer und Evaluation verfügbar.

  • 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 Erstellen komprimierter Tabellen und Indizes.

  • 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, auf dem SQL Server 2005 Enterprise Edition oder SQL Server 2008 ausgeführt wird, 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ä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 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 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 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. 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 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.

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 Database Engine (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

    *Durch Festlegen von ANSI_WARNINGS auf ON wird implizit ARITHABORT auf ON festgelegt, wenn der Kompatibilitätsgrad der Datenbank auf 90 oder größer festgelegt ist. Wird der Kompatibilitätsgrad der Datenbank auf 80 oder niedriger festgelegt, muss die ARITHABORT-Option 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.

Wichtiger HinweisWichtig

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

    Selbstjoin

    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 Database Engine (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 darf nicht kleiner als 80 sein. Der Kompatibilitätsgrad einer Datenbank, die eine indizierte Sicht enthält, darf nicht auf einen Wert festgelegt werden, der kleiner als 80 ist.

Gefilterte Indizes

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

Erforderliche SET-Optionen für gefilterte Indizes

Die SET-Optionen in der Required Value-Spalte 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 größer 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 Richtlinien für den Entwurf gefilterter Indizes.

Räumliche Indizes

Weitere Informationen zu räumlichen Indizes finden Sie unter CREATE SPATIAL INDEX (Transact-SQL) und Arbeiten mit Räumlichkeitsindizes (Datenbankmodul).

XML-Indizes

Weitere Informationen zu XML-Indizes finden Sie unter CREATE XML INDEX (Transact-SQL) und Indizes für Spalten des Datentyps XML.

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

Nicht gruppierte Indizes können Nichtschlüsselspalten auf der Blattebene des Index enthalten. Diese Spalten werden vom Database Engine (Datenbankmodul) beim Berechnen der Indexschlüsselgröße nicht berücksichtigt. Weitere Informationen finden Sie unter Index mit eingeschlossenen Spalten.

HinweisHinweis

Wenn die Partitionierungsschlüsselspalten beim Partitionieren von Tabellen nicht bereits in einem eindeutigen gruppierten Index vorhanden sind, werden sie dem Index vom Database Engine (Datenbankmodul) hinzugefügt. In einem nicht eindeutigen gruppierten Index darf die kombinierte Größe der indizierten Spalten (ohne eingeschlossene Spalten) plus aller hinzugefügten Partitionierungsspalten 1800 Bytes nicht überschreiten.

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

Datenkomprimierung

Die Datenkomprimierung wird im Thema Erstellen komprimierter Tabellen und Indizes 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>... REBUILD PARTITION... Syntax wird die angegebene Partition des Index neu erstellt.

  • Mit der ALTER INDEX <Index>... REBUILD WITH...-Syntax werden alle Partitionen des Index 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 BusinessEntityID der Tabelle Purchasing.ProductVendor erstellt.

USE AdventureWorks2008R2;
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 AdventureWorks2008R2
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 AdventureWorks2008R2;
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 AdventureWorks2008R2;
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 wird die Option IGNORE_DUP_KEY allerdings auf OFF festgelegt.

USE AdventureWorks2008R2;
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 AdventureWorks2008R2;
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 AdventureWorks2008R2;
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 AdventureWorks2008R2;
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 AdventureWorks2008R2;
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 Spalte in der 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 AdventureWorks2008R2;
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 Index 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 Index und die Zeilenkomprimierung für die Partitionen 2 bis 4 des Index 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