Erstellen gefilterter IndizesCreate Filtered Indexes

Anwendungsbereich:Applies to: JaSQL ServerSQL Server (alle unterstützten Versionen) yesSQL ServerSQL Server (all supported versions) JaAzure SQL-DatenbankAzure SQL DatabaseYesAzure SQL-DatenbankAzure SQL DatabaseAnwendungsbereich:Applies to: JaSQL ServerSQL Server (alle unterstützten Versionen) yesSQL ServerSQL Server (all supported versions) JaAzure SQL-DatenbankAzure SQL DatabaseYesAzure SQL-DatenbankAzure SQL Database

In diesem Thema wird beschrieben, wie ein gefilterter Index in SQL Server 2019 (15.x)SQL Server 2019 (15.x) mit SQL Server Management StudioSQL Server Management Studio oder Transact-SQLTransact-SQLerstellt wird.This topic describes how to create a filtered index in SQL Server 2019 (15.x)SQL Server 2019 (15.x) by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL. Ein gefilterter Index ist ein optimierter nicht gruppierter Index, der sich besonders für Abfragen eignet, bei denen aus einer fest definierten Teilmenge von Daten ausgewählt wird.A filtered index is an optimized nonclustered index especially suited to cover queries that select from a well-defined subset of data. Dieser verwendet ein Filterprädikat, um einen Teil der Zeilen in der Tabelle zu indizieren.It uses a filter predicate to index a portion of rows in the table. Mit einem sorgfältig entworfenen gefilterten Index können im Vergleich zu Tabellenindizes die Abfrageleistung verbessert und der Aufwand für die Indexverwaltung und die Indexspeicherung reduziert werden.A well-designed filtered index can improve query performance as well as reduce index maintenance and storage costs compared with full-table indexes.

Gefilterte Indizes können gegenüber Tabellenindizes folgende Vorteile bieten:Filtered indexes can provide the following advantages over full-table indexes:

  • Verbesserte Abfrageleistung und PlanqualitätImproved query performance and plan quality

    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.A well-designed filtered index improves query performance and execution plan quality because it is smaller than a full-table nonclustered index and has filtered statistics. Die gefilterten Statistiken sind genauer als Tabellenstatistiken, da diese nur die Zeilen im gefilterten Index umfassen.The filtered statistics are more accurate than full-table statistics because they cover only the rows in the filtered index.

  • Reduzierter Aufwand bei der IndexverwaltungReduced index maintenance costs

    Ein Index wird nur beibehalten, wenn DML-Anweisungen (Data Manipulation Language) die Daten im Index beeinflussen.An index is maintained only when data manipulation language (DML) statements affect the data in the index. 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.A filtered index reduces index maintenance costs compared with a full-table nonclustered index because it is smaller and is only maintained when the data in the index is changed. Eine große Anzahl von gefilterten Indizes ist insbesondere dann von Vorteil, wenn diese Daten enthalten, die nur selten geändert werden.It is possible to have a large number of filtered indexes, especially when they contain data that is changed infrequently. 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.Similarly, if a filtered index contains only the frequently modified data, the smaller size of the index reduces the cost of updating the statistics.

  • Reduzierter Aufwand bei der IndexspeicherungReduced index storage costs

    Ein gefilterter Index kann den Speicherplatzbedarf von nicht gruppierten Indizes reduzieren, wenn ein Tabellenindex nicht erforderlich ist.Creating a filtered index can reduce disk storage for nonclustered indexes when a full-table index is not necessary. Sie können einen nicht gruppierten Tabellenindex durch mehrere gefilterte Indizes ersetzen, ohne damit die Speicherplatzanforderungen wesentlich zu erhöhen.You can replace a full-table nonclustered index with multiple filtered indexes without significantly increasing the storage requirements.

In diesem ThemaIn This Topic

VorbereitungenBefore You Begin

EntwurfsaspekteDesign Considerations

  • Wenn eine Spalte nur wenig relevante Werte für Abfragen aufweist, können Sie für die Teilmenge der Werte einen gefilterten Index erstellen.When a column only has a small number of relevant values for queries, you can create a filtered index on the subset of values. Wenn beispielsweise die Werte in einer Spalte größtenteils NULL sind und die Abfrage nur die Werte ungleich NULL berücksichtigt, können Sie für die Datenzeilen mit den Werten ungleich NULL einen gefilterten Index erstellen.For example, when the values in a column are mostly NULL and the query selects only from the non-NULL values, you can create a filtered index for the non-NULL data rows. Der resultierende Index ist kleiner und verursacht weniger Verwaltungsaufwand als ein nicht gruppierter Tabellenindex, der für dieselben Schlüsselspalten festgelegt wird.The resulting index will be smaller and cost less to maintain than a full-table nonclustered index defined on the same key columns.

  • Wenn eine Tabelle heterogene Datenzeilen enthält, können Sie einen gefilterten Index für eine oder mehrere Datenkategorien erstellen.When a table has heterogeneous data rows, you can create a filtered index for one or more categories of data. Dies kann die Leistung der Abfragen auf diesen Datenzeilen verbessern, indem es den Fokus einer Abfrage auf einen bestimmten Bereich der Tabelle eingrenzt.This can improve the performance of queries on these data rows by narrowing the focus of a query to a specific area of the table. Auch hier ist der resultierende Index kleiner und verursacht weniger Verwaltungsaufwand als ein nicht gruppierter Tabellenindex.Again, the resulting index will be smaller and cost less to maintain than a full-table nonclustered index.

EinschränkungenLimitations and Restrictions

  • Sie können keinen gefilterten Index für eine Sicht erstellen.You cannot create a filtered index on a view. Der Abfrageoptimierer kann jedoch von einem für eine Tabelle definierten gefilterten Index profitieren, auf den in einer Sicht verwiesen wird.However, the query optimizer can benefit from a filtered index defined on a table that is referenced in a view. Der Abfrageoptimierer berücksichtigt einen gefilterten Index für eine Abfrage, die aus einer Sicht auswählt, wenn die Ergebnisse der Abfrage korrekt sind.The query optimizer considers a filtered index for a query that selects from a view if the query results will be correct.

  • Sie können keinen gefilterten Index in einer Tabelle erstellen, wenn die im Filterausdruck aufgerufene Spalte dem Datentyp CLR entspricht.You cannot create a filtered index on a table when the column accessed in the filter expression is of a CLR data type.

  • Gefilterte Indizes haben die folgenden Vorteile gegenüber indizierten Sichten:Filtered indexes have the following advantages over indexed views:

    • Reduzierter Aufwand bei der Indexverwaltung.Reduced index maintenance costs. Im Vergleich zu einer indizierten Sicht benötigt der Abfrageprozessor weniger CPU-Ressourcen, um einen gefilterten Index zu aktualisieren.For example, the query processor uses fewer CPU resources to update a filtered index than an indexed view.

    • Verbesserte Planqualität.Improved plan quality. Während der Abfragekompilierung wählt der Abfrageoptimierer in vielen Situationen bevorzugt einen gefilterten Index anstelle der äquivalenten indizierten Sicht aus.For example, during query compilation, the query optimizer considers using a filtered index in more situations than the equivalent indexed view.

    • Neuerstellung von online geschalteten Indizes.Online index rebuilds. Sie können gefilterte Indizes neu erstellen, während die Indizes für Abfragen verfügbar sind.You can rebuild filtered indexes while they are available for queries. Die Neuerstellung von online geschalteten Indizes wird bei indizierten Sichten nicht unterstützt.Online index rebuilds are not supported for indexed views. Weitere Informationen finden Sie unter der REBUILD-Option für ALTER INDEX (Transact-SQL).For more information, see the REBUILD option for ALTER INDEX (Transact-SQL).

    • Nicht eindeutige IndizesNon-unique indexes. Gefilterte Indizes können nicht eindeutig sein, wohingegen indizierte Sichten eindeutig sein müssen.Filtered indexes can be non-unique, whereas indexed views must be unique.

  • Gefilterte Indizes werden für eine Tabelle definiert und unterstützen nur einfache Vergleichsoperatoren.Filtered indexes are defined on one table and only support simple comparison operators. Wenn Sie einen Filterausdruck benötigen, der auf mehrere Tabellen verweist oder eine komplexe Logik aufweist, sollten Sie eine Sicht erstellen.If you need a filter expression that references multiple tables or has complex logic, you should create a view. Gefilterte Indizes unterstützen keine LIKE-Operatoren.Filtered indexes do not support LIKE operators.

  • 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.A column in the filtered index expression does not need to be a key or included column in the filtered index definition if the filtered index expression is equivalent to the query predicate and the query does not return the column in the filtered index expression with the query results.

  • 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.A column in the filtered index expression should be a key or included column in the filtered index definition if the query predicate uses the column in a comparison that is not equivalent to the filtered index expression.

  • 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.A column in the filtered index expression should be a key or included column in the filtered index definition if the column is in the query result set.

  • Der Schlüssel des gruppierten Indexes für die Tabelle muss in der Definition des gefilterten Indexes keine Schlüsselspalte oder eingeschlossene Spalte sein.The clustered index key of the table does not need to be a key or included column in the filtered index definition. Der Schlüssel des gruppierten Indexes ist automatisch in allen nicht gruppierten Indizes enthalten, wozu auch gefilterte Indizes zählen.The clustered index key is automatically included in all nonclustered indexes, including filtered indexes.

  • 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.If the comparison operator specified in the filtered index expression of the filtered index results in an implicit or explicit data conversion, an error will occur if the conversion occurs on the left side of a comparison operator. Eine mögliche Lösung besteht darin, den gefilterten Indexausdruck mit dem Datenkonvertierungsoperator (CAST oder CONVERT) auf die rechte Seite des Vergleichsoperators zu schreiben.A solution is to write the filtered index expression with the data conversion operator (CAST or CONVERT) on the right side of the comparison operator.

  • Überprüfen Sie die erforderlichen SET-Optionen für die Erstellung gefilterter Indizes in der CREATE INDEX (Transact-SQL)-SyntaxReview the required SET options for filtered index creation in CREATE INDEX (Transact-SQL) syntax

SicherheitSecurity

BerechtigungenPermissions

Erfordert die ALTER-Berechtigung in der Tabelle oder Sicht.Requires ALTER permission on the table or view. Der Benutzer muss ein Mitglied der festen Serverrolle sysadmin bzw. der festen Datenbankrollen db_ddladmin und db_owner sein.User must be a member of the sysadmin fixed server role or the db_ddladmin and db_owner fixed database roles. Verwenden Sie CREATE INDEX WITH DROP_EXISTING, um den gefilterten Indexausdruck zu ändern.To modify the filtered index expression, use CREATE INDEX WITH DROP_EXISTING.

Verwenden von SQL Server Management StudioUsing SQL Server Management Studio

So erstellen Sie einen gefilterten IndexTo create a filtered index

  1. Klicken Sie in Objekt-Explorer auf das Pluszeichen, um die Datenbank zu erweitern, die die Tabelle enthält, in der Sie einen gefilterten Index erstellen möchten.In Object Explorer, click the plus sign to expand the database that contains the table on which you want to create a filtered index.

  2. Klicken Sie auf das Pluszeichen, um den Ordner Tabellen zu erweitern.Click the plus sign to expand the Tables folder.

  3. Klicken Sie auf das Pluszeichen, um die Tabelle zu erweitern, in der Sie einen gefilterten Index erstellen möchten.Click the plus sign to expand the table on which you want to create a filtered index.

  4. Klicken Sie mit der rechten Maustaste auf den Ordner Indizes, zeigen Sie auf Neuer Index, und wählen Sie Nicht gruppierter Index... aus.Right-click the Indexes folder, point to New Index, and select Non-Clustered Index....

  5. Geben Sie in das Dialogfeld Neuer Index auf der Seite Allgemein den Namen des neuen Indexes in das Feld Indexname ein.In the New Index dialog box, on the General page, enter the name of the new index in the Index name box.

  6. Klicken Sie unter Indexschlüsselspalten auf Hinzufügen… .Under Index key columns, click Add....

  7. Aktivieren Sie im Dialogfeld Spalten auswählen ausName_Tabelle die Kontrollkästchen der Tabellenspalten, die dem eindeutigen Index hinzugefügt werden sollen.In the Select Columns fromtable_name dialog box, select the check box or check boxes of the table column or columns to be added to the unique index.

  8. Klicken Sie auf OK.Click OK.

  9. Geben Sie auf der Seite Filter unter Filterausdruck den SQL-Ausdruck ein, mit dem Sie den gefilterten Index erstellen.On the Filter page, under Filter Expression, enter SQL expression that you'll use to create the filtered index.

  10. Klicken Sie auf OK.Click OK.

Verwenden von Transact-SQLUsing Transact-SQL

So erstellen Sie einen gefilterten IndexTo create a filtered index

  1. Stellen Sie im Objekt-Explorer eine Verbindung mit einer Datenbank-EngineDatabase Engine-Instanz her.In Object Explorer, connect to an instance of Datenbank-EngineDatabase Engine.

  2. Klicken Sie in der Standardleiste auf Neue Abfrage.On the Standard bar, click New Query.

  3. Kopieren Sie das folgende Beispiel, fügen Sie es in das Abfragefenster ein, und klicken Sie auf Ausführen.Copy and paste the following example into the query window and click Execute.

    USE AdventureWorks2012;  
    GO  
    -- Looks for an existing filtered index named "FIBillOfMaterialsWithEndDate"  
    -- and deletes it from the table Production.BillOfMaterials if found.   
    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  
    -- Creates a filtered index "FIBillOfMaterialsWithEndDate"  
    -- on the table Production.BillOfMaterials   
    -- using the columms ComponentID and StartDate.  
    
    CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate  
        ON Production.BillOfMaterials (ComponentID, StartDate)  
        WHERE EndDate IS NOT NULL ;  
    GO  
    

    Der obenstehende gefilterte Index ist für die folgende Abfrage gültig.The filtered index above is valid for the following query. Sie können den Abfrageausführungsplan anzeigen, um zu bestimmen, ob der Abfrageoptimierer den gefilterten Index verwendet hat.You can display the query execution plan to determine if the query optimizer used the filtered index.

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

So stellen Sie sicher, dass ein gefilterter Index in einer SQL-Abfrage verwendet wirdTo ensure that a filtered index is used in a SQL query

  1. Stellen Sie im Objekt-Explorer eine Verbindung mit einer Datenbank-EngineDatabase Engine-Instanz her.In Object Explorer, connect to an instance of Datenbank-EngineDatabase Engine.

  2. Klicken Sie in der Standardleiste auf Neue Abfrage.On the Standard bar, click New Query.

  3. Kopieren Sie das folgende Beispiel, fügen Sie es in das Abfragefenster ein, und klicken Sie auf Ausführen.Copy and paste the following example into the query window and click Execute.

    USE AdventureWorks2012;  
    GO  
    SELECT ComponentID, StartDate FROM Production.BillOfMaterials  
        WITH ( INDEX ( FIBillOfMaterialsWithEndDate ) )   
    WHERE EndDate IN ('20000825', '20000908', '20000918');   
    GO  
    

Weitere Informationen finden Sie unter CCREATE INDEX (Transact-SQL).For more information, see CREATE INDEX (Transact-SQL).