Index mit eingeschlossenen Spalten

Verwenden eingeschlossener Spalten zum Vermeiden von Größenbegrenzungen

Sie können Nichtschlüsselspalten in einen nicht gruppierten Index einschließen, damit die Größenbegrenzungen des aktuellen Indexes von maximal 16 Schlüsselspalten und einer maximalen Größe des Indexschlüssels von 900 Byte nicht überschritten werden. Nichtschlüsselspalten werden von Database Engine (Datenbankmodul) beim Berechnen der Indexschlüsselspalten oder Indexschlüsselgröße nicht berücksichtigt.

Angenommen, Sie möchten z. B. die folgenden Spalten in der Document-Tabelle in der AdventureWorks-Beispieldatenbank indizieren:

     Title nvarchar(50)

     Revision nchar(5)

     FileName nvarchar(400)

Da für die Datentypen nchar und nvarchar 2 Byte für jedes Zeichen erforderlich sind, überschreitet ein Index, der diese drei Spalten enthält, die Größenbegrenzung von 900 Byte um 10 Byte (455 * 2). Indem die INCLUDE-Klausel der CREATE INDEX-Anweisung verwendet wird, kann der Indexschlüssel als (Title, Revision) und FileName als Nichtschlüsselspalte definiert werden. Auf diese Weise beträgt die Größe des Indexschlüssels 110 Byte (55 * 2), und der Index enthält dennoch alle erforderlichen Spalten. Die folgende Anweisung erstellt einen solchen Index:

USE AdventureWorks;
GO
CREATE INDEX IX_Document_Title       
ON Production.Document (Title, Revision)       
INCLUDE (FileName);       

Richtlinien für Indizes mit eingeschlossenen Spalten

Wenn Sie nicht gruppierte Indizes mit eingeschlossenen Spalten entwerfen, sollten Sie die folgenden Richtlinien beachten:

• Nichtschlüsselspalten werden in der INCLUDE-Klausel der CREATE INDEX-Anweisung definiert.

• Nichtschlüsselspalten können nur für nicht gruppierte Indizes für Tabellen oder indizierte Sichten definiert werden.

• Alle Datentypen mit Ausnahme von text, ntext und image sind zulässig.

• Berechnete Spalten, die deterministisch und entweder präzise oder unpräzise sind, können als eingeschlossene Spalten verwendet werden. Weitere Informationen finden Sie unter Erstellen von Indizes für berechnete Spalten.

• Ebenso wie Schlüsselspalten können berechnete Spalten, die aus image-, ntext- und text-Datentypen abgeleitet werden, Nichtschlüsselspalten (eingeschlossene Spalten) sein, wenn der Datentyp der berechneten Spalte als Nichtschlüssel-Indexspalte zulässig ist.

• Spaltennamen dürfen nicht sowohl in der INCLUDE-Liste als auch in der Schlüsselspaltenliste angegeben werden.

• Spaltennamen können nicht in der INCLUDE-Liste wiederholt werden.

Richtlinien für die Spaltengröße

• Es muss mindestens eine Schlüsselspalte definiert werden. Die maximal zulässige Anzahl der Nichtschlüsselspalten beträgt 1023 Spalten. Dies ist die maximale Anzahl der Tabellenspalten minus 1.

• Indexschlüsselspalten (ausschließlich der Nichtschlüsselspalten) unterliegen der Begrenzung der Indexgröße auf maximal 16 Schlüsselspalten und der Gesamtgröße des Indexschlüssels von 900 Byte.

• Die Gesamtgröße aller Nichtschlüsselspalten wird nur durch die in der INCLUDE-Klausel angegebene Größe der Spalten beschränkt; varchar(max)-Spalten sind z. B. auf 2 GB beschränkt.

Richtlinien für die Spaltenänderung

Wenn Sie eine Tabellenspalte ändern, die als eingeschlossene Spalte definiert wurde, gelten die folgenden Einschränkungen:

• Nichtschlüsselspalten können nur aus der Tabelle gelöscht werden, wenn der Index zuvor gelöscht wird.

• Nichtschlüsselspalten können nur zum Ausführen der folgenden Aufgaben geändert werden:

  • Ändern der NULL-Zulässigkeit der Spalte von NOT NULL in NULL.

  • Vergrößern der Länge von varchar-, nvarchar- oder varbinary-Spalten.

    Hinweis
    Diese Einschränkungen hinsichtlich der Spaltenänderung gelten für Indexschlüsselspalten.

Entwurfsempfehlungen

Überarbeiten Sie nicht gruppierte Indizes mit großen Indexschlüsseln so, dass nur Spalten, die für Suchen und Suchvorgänge verwendet werden, Schlüsselspalten sind. Erklären Sie alle anderen Spalten, die die Abfrage abdecken, zu eingeschlossenen Nichtschlüsselspalten. Auf diese Weise sind alle Spalten vorhanden, die zum Abdecken der Abfrage erforderlich sind, der Indexschlüssel selbst ist jedoch klein und effizient.

Angenommen, Sie möchten z. B. einen Index entwerfen, der die folgende Abfrage abdeckt:

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

Damit die Abfrage abgedeckt wird, muss jede Spalte im Index definiert werden. Sie könnten zwar alle Spalten als Schlüsselspalten definieren, die Schlüsselgröße würde dann aber 334 Byte betragen. Da die einzige Spalte, die tatsächlich als Suchkriterium verwendet wird, die PostalCode-Spalte mit einer Länge von 30 Byte ist, definiert der bessere Indexentwurf PostalCode als Schlüsselspalte und schließt alle anderen Spalten als Nichtschlüsselspalten ein.

Die folgende Anweisung erstellt einen Index mit eingeschlossenen Spalten, um die Abfrage abzudecken:

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

Überlegungen zur Leistung

Vermeiden Sie es, nicht erforderliche Spalten hinzuzufügen. Das Hinzufügen einer zu großen Anzahl von Schlüssel- oder Nichtschlüssel-Indexspalten kann zu folgenden Auswirkungen auf die Leistung führen:

• Es passen weniger Indexzeilen auf eine Seite. Dies kann zu einer Zunahme der E/A und verringerter Cacheeffizienz führen.

• Zum Speichern des Indexes ist eine größere Menge an Speicherplatz erforderlich. Insbesondere das Hinzufügen von varchar(max)-, nvarchar(max)-, varbinary(max)- oder xml-Datentypen als Nichtschlüssel-Indexspalten kann die Anforderungen an den Speicherplatz erheblich erhöhen. Der Grund liegt darin, dass die Spaltenwerte in die Blattebene des Indexes kopiert werden. Daher werden sie sowohl im Index als auch in der Basistabelle gespeichert.

• Die Indexwartung kann zu einem größeren Zeitaufwand für das Ausführen von Änderungen, Einfügungen, Aktualisierungen oder Löschvorgängen an der zugrunde liegenden Tabelle oder indizierten Sicht führen.

Sie müssen überprüfen, ob die Steigerungen der Abfrageleistung die negativen Auswirkungen auf die Leistung während der Datenänderung sowie hinsichtlich zusätzlicher Speicherplatzanforderungen aufwiegen. Weitere Informationen zum Auswerten der Abfrageleistung finden Sie unter Optimieren von Abfragen.

Siehe auch

Konzepte