Freigeben über

  • Artikel

Index mit eingeschlossenen Spalten

Sie können die Funktionen nicht gruppierter Indizes erweitern, indem Sie der Blattebene des nicht gruppierten Indexes Nichtschlüsselspalten hinzufügen. Indem Sie Nichtschlüsselspalten einschließen, erstellen Sie nicht gruppierte Indizes, die eine größere Anzahl von Abfragen abdecken. Dies ist der Fall, weil Nichtschlüsselspalten die folgenden Vorteile aufweisen:

  • Es kann sich um Datentypen handeln, die als Indexschlüsselspalten nicht zulässig sind.

  • Sie werden von Database Engine (Datenbankmodul) beim Berechnen der Indexschlüsselspalten oder Indexschlüsselgröße nicht berücksichtigt.

Ein Index mit eingeschlossenen Nichtschlüsselspalten kann die Abfrageleistung erheblich steigern, wenn alle Spalten in der Abfrage in den Index als Schlüssel- oder Nichtschlüsselspalten eingeschlossen werden. Leistungsvorteile werden erzielt, weil der Abfrageoptimierer alle Spaltenwerte im Index finden kann; auf Daten der Tabelle oder des gruppierten Indexes wird nicht zugegriffen, sodass als Ergebnis weniger Datenträger-E/A-Vorgänge auftreten.

HinweisHinweis

Wenn ein Index alle Spalten enthält, auf die die Abfrage verweist, wird dies normalerweise als Abdecken der Abfrage bezeichnet.

Die Schlüsselspalten werden auf allen Ebenen des Indexes gespeichert, die Nichtschlüsselspalten nur auf der Blattebene. Weitere Informationen zu Indexebenen finden Sie unter Organisationsstruktur von Tabellen und Indizes.

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.

      HinweisHinweis

      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.