建立篩選的索引Create Filtered Indexes

適用於: 是SQL Server是Azure SQL Database否Azure Synapse Analytics (SQL DW)否平行處理資料倉儲APPLIES TO: YesSQL Server YesAzure SQL Database NoAzure Synapse Analytics (SQL DW) NoParallel Data Warehouse

此主題描述如何使用 SQL Server 2019 (15.x)SQL Server 2019 (15.x)SQL Server Management StudioSQL Server Management Studio ,在 Transact-SQLTransact-SQL中建立篩選索引。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. 篩選索引是最佳化的非叢集索引,特別適合涵蓋從妥善定義的資料子集選取而來的查詢。A filtered index is an optimized nonclustered index especially suited to cover queries that select from a well-defined subset of data. 篩選索引會使用篩選述詞對資料表中的部分資料列進行索引。It uses a filter predicate to index a portion of rows in the table. 與完整資料表索引相較,設計良好的篩選索引可以提升查詢效能、降低索引維護成本和儲存成本。A well-designed filtered index can improve query performance as well as reduce index maintenance and storage costs compared with full-table indexes.

篩選索引可以提供全資料表索引所不及的下列優勢:Filtered indexes can provide the following advantages over full-table indexes:

  • 提升的查詢效能和計畫品質Improved query performance and plan quality

    設計良好的篩選索引可以提升查詢效能和執行計畫品質,因為它比全資料表的非叢集索引來得小,且具有篩選統計資料。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. 篩選統計資料比全資料表統計資料更為正確,因為僅涵蓋篩選索引中的資料列。The filtered statistics are more accurate than full-table statistics because they cover only the rows in the filtered index.

  • 降低的索引維護成本Reduced index maintenance costs

    只有在資料操作語言 (DML) 陳述式影響到索引中的資料時,才會對索引進行維護。An index is maintained only when data manipulation language (DML) statements affect the data in the index. 與完整資料表的非叢集索引相較,篩選索引可以降低維護成本,因為後者較小且僅會在索引中的資料已變更時才會進行維護。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. 篩選索引的數量可能很多,特別是當其包含不常變更的資料時。It is possible to have a large number of filtered indexes, especially when they contain data that is changed infrequently. 同樣地,如果篩選索引僅包含經常修改的資料,則因為索引的大小較小,更新統計資料的成本就會下降。Similarly, if a filtered index contains only the frequently modified data, the smaller size of the index reduces the cost of updating the statistics.

  • 降低的索引儲存成本Reduced index storage costs

    在不需要全資料表索引時,建立篩選索引可以縮減非叢集索引的磁碟儲存量。Creating a filtered index can reduce disk storage for nonclustered indexes when a full-table index is not necessary. 您可以使用多個篩選索引來取代全資料表的非叢集索引,而不會大幅增加儲存需求。You can replace a full-table nonclustered index with multiple filtered indexes without significantly increasing the storage requirements.

本主題內容In This Topic

開始之前Before You Begin

設計考量Design Considerations

  • 當資料行僅具有少數的查詢相關值時,可以在值的子集上建立篩選索引。When a column only has a small number of relevant values for queries, you can create a filtered index on the subset of values. 例如,當資料行中的值大部分都是 NULL 且查詢只會從非 NULL 值進行選取時,您可以針對非 NULL 的資料列建立篩選索引。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. 所產生的索引比在相同的索引鍵資料行上定義的全資料表非叢集索引還小,維護成本也比較低。The resulting index will be smaller and cost less to maintain than a full-table nonclustered index defined on the same key columns.

  • 當資料表具有異質資料列時,您可以針對一或多個資料類別建立篩選索引。When a table has heterogeneous data rows, you can create a filtered index for one or more categories of data. 這會將查詢焦點縮小為資料表的特定區域,改善這些資料列的查詢效能。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. 同樣地,所產生的索引比完整資料表非叢集索引還小,維護成本也比較低。Again, the resulting index will be smaller and cost less to maintain than a full-table nonclustered index.

限制事項Limitations and Restrictions

  • 您無法在檢視上建立篩選索引;You cannot create a filtered index on a view. 不過,如果在檢視中參考的資料表上定義篩選索引,則可為查詢最佳化工具提供多項優點。However, the query optimizer can benefit from a filtered index defined on a table that is referenced in a view. 如果查詢結果會是正確的,則查詢最佳化工具會針對從檢視進行選取的查詢考慮篩選索引。The query optimizer considers a filtered index for a query that selects from a view if the query results will be correct.

  • 篩選運算式中存取的資料行為 CLR 資料類型時,您無法在資料表上建立篩選過的索引。You cannot create a filtered index on a table when the column accessed in the filter expression is of a CLR data type.

  • 篩選索引具有索引檢視表所不及的下列優勢:Filtered indexes have the following advantages over indexed views:

    • 降低的索引維護成本。Reduced index maintenance costs. 例如,相較於索引檢視表而言,查詢處理器會使用較少的 CPU 資源來更新篩選索引。For example, the query processor uses fewer CPU resources to update a filtered index than an indexed view.

    • 改善的計畫品質。Improved plan quality. 例如,在查詢編譯期間,查詢最佳化工具考慮使用篩選索引的情況會比對等的索引檢視表更多。For example, during query compilation, the query optimizer considers using a filtered index in more situations than the equivalent indexed view.

    • 線上索引重建。Online index rebuilds. 您可以在篩選索引可用於查詢時,重建篩選索引。You can rebuild filtered indexes while they are available for queries. 線上索引重建不支援索引檢視表。Online index rebuilds are not supported for indexed views. 如需詳細資訊,請參閱 ALTER INDEX (Transact-SQL) 的 REBUILD 選項。For more information, see the REBUILD option for ALTER INDEX (Transact-SQL).

    • 非唯一索引。Non-unique indexes. 篩選索引可以是非唯一的,而索引檢視表則必須是唯一的。Filtered indexes can be non-unique, whereas indexed views must be unique.

  • 篩選索引定義於單一資料表,僅支援簡單比較運算子。Filtered indexes are defined on one table and only support simple comparison operators. 如果需要參考多個資料表或具有複雜邏輯的篩選運算式,則應該建立檢視。If you need a filter expression that references multiple tables or has complex logic, you should create a view.

  • 如果篩選索引運算式相等於查詢述詞,且查詢並未以篩選索引運算式中的資料行傳回查詢結果,則篩選索引運算式中的資料行不需要是篩選索引定義中的索引鍵或內含資料行。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.

  • 如果查詢述詞在不等於篩選索引運算式的比較中使用篩選索引運算式中的資料行,則該資料行應該是篩選索引定義中的索引鍵或內含資料行。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.

  • 如果篩選索引運算式中的資料行在查詢結果集中,則該資料行應該是篩選索引定義中的索引鍵或內含資料行。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.

  • 資料表的叢集索引鍵並不需要是篩選索引定義中的索引鍵或內含資料行。The clustered index key of the table does not need to be a key or included column in the filtered index definition. 叢集索引鍵會自動包含在所有非叢集的索引中 (包含篩選索引在內)。The clustered index key is automatically included in all nonclustered indexes, including filtered indexes.

  • 如果在篩選索引的篩選索引運算式中指定的比較運算子產生隱含或明確的資料轉換,則如果該轉換是發生在比較運算子的左側,就會發生錯誤。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. 解決方案是以資料轉換運算子 (CAST 或 CONVERT) 在比較運算子的右側寫下篩選索引運算式。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.

  • 檢閱 CREATE INDEX (Transact-SQL) 語法中用於建立已篩選索引的必要 SET 選項Review the required SET options for filtered index creation in CREATE INDEX (Transact-SQL) syntax

SecuritySecurity

權限Permissions

需要資料表或檢視表的 ALTER 權限。Requires ALTER permission on the table or view. 使用者必須是 系統管理員 固定伺服器角色的成員,或是 db_ddladmindb_owner 固定資料庫角色的成員。User must be a member of the sysadmin fixed server role or the db_ddladmin and db_owner fixed database roles. 若要修改篩選索引運算式,請使用 CREATE INDEX WITH DROP_EXISTING。To modify the filtered index expression, use CREATE INDEX WITH DROP_EXISTING.

使用 SQL Server Management StudioUsing SQL Server Management Studio

建立篩選的索引To create a filtered index

  1. 在 [物件總管] 中,按一下加號展開資料庫,此資料庫包含您要建立篩選索引的資料表。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. 按一下加號展開 [資料表] 資料夾。Click the plus sign to expand the Tables folder.

  3. 按一下加號展開要建立篩選索引的資料表。Click the plus sign to expand the table on which you want to create a filtered index.

  4. 以滑鼠右鍵按一下 [索引] 資料夾,指向 [新增索引] ,然後選取 [非叢集索引…] 。Right-click the Indexes folder, point to New Index, and select Non-Clustered Index....

  5. [新增索引] 對話方塊,於 [一般] 頁面上的 [索引名稱] 方塊中輸入新索引的名稱。In the New Index dialog box, on the General page, enter the name of the new index in the Index name box.

  6. 按一下 [索引鍵資料行] 下的 [新增...] 。Under Index key columns, click Add....

  7. 在 [從 tablename 選取資料行]_ 對話方塊中,選取要新增至唯一索引之一或多個資料表資料行的一或多個核取方塊。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. 按一下 [確定] 。Click OK.

  9. 在 [篩選] 頁面的 [篩選運算式] 底下,輸入要用來建立篩選索引的 SQL 運算式。On the Filter page, under Filter Expression, enter SQL expression that you'll use to create the filtered index.

  10. 按一下 [確定] 。Click OK.

使用 Transact-SQLUsing Transact-SQL

建立篩選的索引To create a filtered index

  1. [物件總管] 中,連接到 Database EngineDatabase Engine的執行個體。In Object Explorer, connect to an instance of Database EngineDatabase Engine.

  2. 在標準列上,按一下 [新增查詢]On the Standard bar, click New Query.

  3. 複製下列範例並將其貼到查詢視窗中,然後按一下 [執行]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  
    

    以上的篩選索引對下列查詢有效。The filtered index above is valid for the following query. 您可以顯示查詢執行計畫,以判斷查詢最佳化工具是否使用了篩選索引。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  
    

確定在 SQL 查詢中使用篩選的索引To ensure that a filtered index is used in a SQL query

  1. [物件總管] 中,連接到 Database EngineDatabase Engine的執行個體。In Object Explorer, connect to an instance of Database EngineDatabase Engine.

  2. 在標準列上,按一下 [新增查詢]On the Standard bar, click New Query.

  3. 複製下列範例並將其貼到查詢視窗中,然後按一下 [執行]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  
    

如需詳細資訊,請參閱 CREATE INDEX (Transact-SQL)For more information, see CREATE INDEX (Transact-SQL).