Erstellen gefilterter Indizes

  • Artikel

Gilt für:SQL ServerAzure SQL-DatenbankAzure SQL Managed Instance

In diesem Artikel wird beschrieben, wie ein gefilterter Index mithilfe von SQL Server Management Studio (SSMS) oder Transact-SQL erstellt wird. Ein gefilterter Index ist ein optimierter, datenträgerbasierter nicht gruppierter Index, der sich besonders für Abfragen eignet, bei denen aus einer fest definierten Teilmenge von Daten ausgewählt wird. Dieser verwendet ein Filterprädikat, um einen Teil der Zeilen in der Tabelle zu indizieren. Mit einem sorgfältig entworfenen gefilterten Index können die Abfrageleistung verbessert und die Indexwartungs- und Speicherkosten im Vergleich zu Tabellenindizes reduziert werden.

Gefilterte Indizes können gegenüber Tabellenindizes folgende Vorteile bieten:

  1. Verbesserte Abfrageleistung und Planqualität:

    Mit einem sorgfältig entworfenen gefilterten Index wird die Abfrageleistung und die Ausführungsplanqualität verbessert, da dieser kleiner ist als ein nicht gruppierter Tabellenindex und mit gefilterten Statistiken arbeitet. Die gefilterten Statistiken sind genauer als Tabellenstatistiken, da diese nur die Zeilen im gefilterten Index umfassen.

  2. Reduzierter Aufwand bei der Indexverwaltung.

    Ein Index wird nur beibehalten, wenn DML-Anweisungen (Data Manipulation Language) die Daten im Index beeinflussen. Ein gefilterter Index reduziert im Vergleich zu einem nicht gruppierten Tabellenindex den Aufwand für die Indexverwaltung, da dieser kleiner ist und nur beibehalten wird, wenn die Daten im Index geändert werden. Eine große Anzahl von gefilterten Indizes ist insbesondere dann von Vorteil, wenn diese Daten enthalten, die nur selten geändert werden. Ebenso reduziert die geringere Indexgröße den Aufwand für das Aktualisieren der Statistiken, wenn ein gefilterter Index nur die häufig geänderten Daten enthält.

  3. Reduzierte Kosten für die Indexspeicherung:

    Ein gefilterter Index kann den Speicherplatzbedarf von nicht gruppierten Indizes reduzieren, wenn ein Tabellenindex nicht erforderlich ist. Sie können einen nicht gruppierten Tabellenindex durch mehrere gefilterte Indizes ersetzen, ohne damit die Speicherplatzanforderungen wesentlich zu erhöhen.

Überlegungen zum Entwurf

Wenn eine Spalte nur wenig relevante Werte für Abfragen aufweist, können Sie für die Teilmenge der Werte einen gefilterten Index erstellen. Der resultierende Index ist kleiner und verursacht weniger Verwaltungsaufwand als ein nicht gruppierter Tabellenindex, der für dieselben Schlüsselspalten festgelegt wird.

Betrachten Sie beispielsweise einen gefilterten Index in den folgenden Datenszenarios. In jedem Fall sollte die WHERE-Klausel der Abfrage eine Teilmenge der WHERE-Klausel des gefilterten Index sein, um vom gefilterten Index zu profitieren.

  • Wenn die Werte in einer Spalte größtenteils NULL sind und die Abfrage nur einen aus den NULL-Werten auswählt: Sie können einen gefilterten Index für die Datenzeilen erstellen, die nicht NULL sind.
  • Wenn Zeilen in einer Tabelle von einem wiederkehrenden Workflow oder Warteschlangenprozess als verarbeitet markiert werden: Im Laufe der Zeit werden die meisten der Zeilen in der Tabelle als verarbeitet markiert. Ein gefilterter Index für Zeilen, die noch nicht verarbeitet wurden, würde von der wiederkehrenden Abfrage profitieren, die nach Zeilen sucht, die noch nicht verarbeitet wurden.
  • Wenn eine Tabelle heterogene Datenzeilen enthält: Sie können einen gefilterten Index für eine oder mehrere Datenkategorien erstellen. Dies kann die Leistung der Abfragen auf diesen Datenzeilen verbessern, indem es den Fokus einer Abfrage auf einen bestimmten Bereich der Tabelle eingrenzt. Auch hier ist der resultierende Index kleiner und verursacht weniger Verwaltungsaufwand als ein nicht gruppierter Tabellenindex.

Einschränkungen

  • Sie können keinen gefilterten Index für eine Anzeige erstellen. Der Abfrageoptimierer kann jedoch von einem für eine Tabelle definierten gefilterten Index profitieren, auf den in einer Sicht verwiesen wird. Der Abfrageoptimierer berücksichtigt einen gefilterten Index für eine Abfrage, die aus einer Sicht auswählt, wenn die Ergebnisse der Abfrage korrekt sind.

  • Sie können keinen gefilterten Index in einer Tabelle erstellen, wenn die im Filterausdruck aufgerufene Spalte dem Datentyp CLR entspricht.

  • Gefilterte Indizes haben die folgenden Vorteile gegenüber indizierten Sichten:

    • Reduzierter Aufwand bei der Indexverwaltung. Im Vergleich zu einer indizierten Sicht benötigt der Abfrageprozessor weniger CPU-Ressourcen, um einen gefilterten Index zu aktualisieren.

    • Verbesserte Planqualität. Während der Abfragekompilierung wählt der Abfrageoptimierer in vielen Situationen bevorzugt einen gefilterten Index anstelle der äquivalenten indizierten Sicht aus.

    • Neuerstellung von online geschalteten Indizes. Sie können gefilterte Indizes neu erstellen, während die Indizes für Abfragen verfügbar sind. Die Neuerstellung von online geschalteten Indizes wird bei indizierten Sichten nicht unterstützt. Weitere Informationen finden Sie unter REBUILDALTER INDEX (Transact-SQL).

    • Nicht eindeutige Indizes Gefilterte Indizes können nicht eindeutig sein, wohingegen indizierte Sichten eindeutig sein müssen.

  • Gefilterte Indizes werden für eine Tabelle definiert und unterstützen nur einfache Vergleichsoperatoren. Wenn Sie einen Filterausdruck benötigen, der auf mehrere Tabellen verweist oder eine komplexe Logik aufweist, sollten Sie eine Sicht erstellen. Gefilterte Indizes unterstützen keine LIKE-Operatoren.

  • Eine Spalte im gefilterten Indexausdruck muss in der Definition des gefilterten Indexes keine Schlüsselspalte oder eingeschlossene Spalte sein, wenn der gefilterte Indexausdruck dem Abfrageprädikat entspricht und die Abfrage die Spalte im gefilterten Indexausdruck mit den Abfrageergebnissen nicht zurückgibt.

  • Eine Spalte im gefilterten Indexausdruck sollte in der Definition des gefilterten Indexes eine Schlüsselspalte oder eingeschlossene Spalte sein, wenn das Abfrageprädikat die Spalte in einem Vergleich verwendet, der nicht dem gefilterten Indexausdruck entspricht.

  • Eine Spalte im gefilterten Indexausdruck sollte in der Definition des gefilterten Indexes eine Schlüsselspalte oder eingeschlossene Spalte sein, wenn die Spalte im Abfrageresultset enthalten ist.

  • Der Schlüssel des gruppierten Indexes für die Tabelle muss in der Definition des gefilterten Indexes keine Schlüsselspalte oder eingeschlossene Spalte sein. Der Schlüssel des gruppierten Indexes ist automatisch in allen nicht gruppierten Indizes enthalten, wozu auch gefilterte Indizes zählen. Weitere Informationen finden Sie unter Leitfaden zur Architektur und zum Design von SQL Server-Indizes.

  • Wenn der im gefilterten Indexausdruck der gefilterten Indexergebnisse angegebene Vergleichsoperator eine implizite oder explizite Datenkonvertierung ergibt, kommt es zu einem Fehler, wenn die Konvertierung auf der linken Seite eines Vergleichsoperators auftritt. Eine mögliche Lösung besteht darin, den gefilterten Indexausdruck mit dem Datenkonvertierungsoperator (CAST oder CONVERT) auf die rechte Seite des Vergleichsoperators zu schreiben.

  • Überprüfen Sie die erforderlichen SET-Optionen für die Erstellung gefilterter Indizes in der CREATE INDEX (Transact-SQL)-Syntax

  • Filter können nicht auf Primärschlüsseleinschränkungen oder eindeutige Einschränkungen angewendet werden, können aber auf Indizes mit der Eigenschaft UNIQUE angewendet werden.

  • Sie können keinen primären XML-Index für eine berechnete Spalte erstellen.

Berechtigungen

Erfordert die ALTER-Berechtigung in der Tabelle oder Sicht. Der Benutzer bzw. die Benutzerin muss ein Mitglied der festen Serverrolle sysadmin bzw. der festen Datenbankrollen db_ddladmin und db_owner sein. Verwenden Sie CREATE INDEX WITH DROP_EXISTING, um den gefilterten Indexausdruck zu ändern.

Erstellen eines gefilterten Index mit dem SSMS

  1. Klicken Sie im Objekt-Explorer auf das Pluszeichen, um die Datenbank zu erweitern, die die Tabelle enthält, in der Sie einen gefilterten Index erstellen möchten.

  2. Klicken Sie auf das Pluszeichen, um den Ordner Tabellen zu erweitern.

  3. Klicken Sie auf das Pluszeichen, um die Tabelle zu erweitern, in der Sie einen gefilterten Index erstellen möchten.

  4. Klicken Sie mit der rechten Maustaste auf den Ordner Indizes, zeigen Sie auf Neuer Index, und wählen Sie Nicht gruppierter Index... aus.

  5. Geben Sie in das Dialogfeld Neuer Index auf der Seite Allgemein den Namen des neuen Indexes in das Feld Indexname ein.

  6. Klicken Sie unter Indexschlüsselspalten auf Hinzufügen....

  7. Aktivieren Sie im Dialogfeld Spalten auswählen austable_name das oder die Kontrollkästchen der Tabellenspalte oder der Spalten, die dem Index hinzugefügt werden sollen.

  8. Wählen Sie OK aus.

  9. Geben Sie auf der Seite Filter unter Filterausdruck den SQL-Ausdruck ein, mit dem Sie den gefilterten Index erstellen.

  10. Wählen Sie OK aus.

Erstellen eines gefilterten Index mit Transact-SQL

Die Transact-SQL-Codebeispiele in diesem Artikel verwenden die AdventureWorks2022-Beispieldatenbank, die Sie von der Homepage Microsoft SQL Server Samples and Community Projects herunterladen können.

  1. Stellen Sie im Objekt-Explorer eine Verbindung mit einer Datenbank-Engine-Instanz her.

  2. Klicken Sie in der Standardleiste auf Neue Abfrage.

  3. Kopieren Sie das folgende Beispiel in das Abfragefenster, und klicken Sie dann auf Ausführen.

USE AdventureWorks2022;
GO

DROP INDEX IF EXISTS FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials
GO

CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL ;
GO

Der gefilterte Index FIBillOfMaterialsWithEndDate ist für die folgende Abfrage gültig. Sie können den Abfrageausführungsplan anzeigen, um zu ermitteln, ob der Abfrageoptimierer den gefilterten Index verwendet hat.

USE AdventureWorks2022;
GO

SELECT ProductAssemblyID, ComponentID, StartDate
FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL
    AND ComponentID = 5
    AND StartDate > '01/01/2008' ;
GO

Nächste Schritte

Weitere Informationen zum Erstellen von Indizes und verwandten Themen finden Sie in den folgenden Artikeln: