Erstellen gefilterter Indizes

Artikel
04/27/2023

Gilt für:SQL Server Azure SQL-Datenbank Azure SQL Managed Instance

In diesem Artikel wird beschrieben, wie Sie einen gefilterten Index mit SQL Server Management Studio (SSMS) oder Transact-SQL erstellen. 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:

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.
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.
Reduzierte Kosten für die Indexspeicherung:

Das Erstellen eines gefilterten Indexes kann den Datenträgerspeicher für nicht gruppierte Indizes reduzieren, wenn kein Volltabellenindex 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 einige relevante Werte für Abfragen enthält, können Sie einen gefilterten Index für die Teilmenge der Werte 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 Indexes 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 Zeilen in der Tabelle als verarbeitet markiert. Ein gefilterter Index für Zeilen, die noch nicht verarbeitet werden, 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.

Beschränkungen und Einschränkungen

Sie können keinen gefilterten Index für eine Ansicht 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 für eine Tabelle erstellen, wenn die Spalte, auf die im Filterausdruck zugegriffen wird, einen CLR-Datentyp aufweist.
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. Onlineindex-Neuerstellungen werden für indizierte Ansichten nicht unterstützt. Weitere Informationen finden Sie in der Option für ALTER INDEX (Transact-SQL).For more information, see the REBUILD option for ALTER 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 LIKE keine Operatoren.
Eine Spalte im gefilterten Indexausdruck muss kein Schlüssel oder eine eingeschlossene Spalte in der gefilterten Indexdefinition sein, wenn der gefilterte Indexausdruck dem Abfrage-Prädikat entspricht und die Abfrage die Spalte im gefilterten Indexausdruck nicht mit den Abfrageergebnissen zurückgibt.
Eine Spalte im gefilterten Indexausdruck sollte ein Schlüssel oder eine eingeschlossene Spalte in der gefilterten Indexdefinition sein, wenn das Abfrage-Prä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 gruppierte Indexschlüssel der Tabelle muss kein Schlüssel oder eine Spalte in der gefilterten Indexdefinition 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 Lösung besteht darin, den gefilterten Indexausdruck mit dem Datenkonvertierungsoperator (CAST oder CONVERT) auf der rechten Seite des Vergleichsoperators zu schreiben.
Überprüfen der erforderlichen SET Optionen für die gefilterte Indexerstellung in CREATE INDEX (Transact-SQL) -Syntax
Filter können nicht auf Primärschlüssel oder eindeutige Einschränkungen angewendet werden, können aber auf Indizes mit der UNIQUE Eigenschaft angewendet werden.
Sie können keinen gefilterten 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 Indexes mit SSMS

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.
Klicken Sie auf das Pluszeichen, um den Ordner Tabellen zu erweitern.
Klicken Sie auf das Pluszeichen, um die Tabelle zu erweitern, in der Sie einen gefilterten Index erstellen möchten.
Klicken Sie mit der rechten Maustaste auf den Ordner Indizes, zeigen Sie auf Neuer Index, und wählen Sie Nicht gruppierter Index... aus.
Geben Sie in das Dialogfeld Neuer Index auf der Seite Allgemein den Namen des neuen Indexes in das Feld Indexname ein.
Klicken Sie unter Indexschlüsselspalten auf Hinzufügen....
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.
Klicken Sie auf OK.
Geben Sie auf der Seite Filter unter Filterausdruck den SQL-Ausdruck ein, mit dem Sie den gefilterten Index erstellen.
Klicken Sie auf OK.

Erstellen eines gefilterten Indexes mit Transact-SQL

Dieser Artikel erfordert die AdventureWorks2022-Beispieldatenbank, die Sie von der Homepage Microsoft SQL Server Samples and Community Projects herunterladen können.

Stellen Sie im Objekt-Explorer eine Verbindung mit einer Datenbank-Engine-Instanz her.
Klicken Sie in der Standardleiste auf Neue Abfrage.
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: