共用方式為

建立篩選索引

  • 發行項

適用於：Microsoft Fabric 中的 SQL ServerAzure SQL 資料庫 Azure SQL 受控執行個體 SQL 資料庫

本文說明如何使用 SQL Server Management Studio (SSMS) 或 Transact-SQL 來建立篩選索引。 篩選索引是最佳化磁碟型資料列存放區的非叢集索引，特別適合涵蓋從妥善定義的資料子集所選取的查詢。 篩選索引會使用篩選述詞對資料表中的部分資料列進行索引。 與完整資料表索引相比，設計良好的篩選索引可以提升查詢效能、降低索引維護成本和儲存成本。

篩選索引可以提供全資料表索引所不及的下列優勢：

  1. 提升的查詢效能和計畫品質。

    設計良好的篩選索引可以提升查詢效能和執行計畫品質，因為它比全資料表的非叢集索引來得小，且具有篩選統計資料。 篩選統計資料比全資料表統計資料更為正確，因為僅涵蓋篩選索引中的資料列。

  2. 降低的索引維護成本。

    只有在資料操作語言 (DML) 陳述式影響到索引中的資料時，才會對索引進行維護。 與完整資料表的非叢集索引相較，篩選索引可以降低維護成本，因為後者較小且僅會在索引中的資料已變更時才會進行維護。 篩選索引的數量可能很多，特別是當其包含不常變更的資料時。 同樣地，如果篩選索引僅包含經常修改的資料，則因為索引的大小較小，更新統計資料的成本就會下降。

  3. 降低的索引儲存成本。

    在不需要完整資料表索引的情況下，建立篩選索引可以降低非叢集索引的磁碟儲存量。 您可以使用多個篩選索引來取代全資料表的非叢集索引，而不會大幅增加儲存需求。

設計考量

當資料行僅具有少數的查詢相關值時，您可以在值的子集上建立篩選索引。 所產生的索引比在相同的索引鍵資料行上定義的全資料表非叢集索引還小，維護成本也比較低。

例如，請考慮建立下列資料案例中的篩選索引。 在每個案例中，查詢的 WHERE 子句應該是篩選索引的 WHERE 子句子集，藉此受益於篩選索引。

  • 當資料行的值大部分為 NULL，且僅從非 NULL 值選取查詢時。 您可以針對非 NULL 資料列建立篩選索引。
  • 當週期性工作流程或佇列流程將資料表的資料列標示為「已處理」時。 經過一段時間後，資料表中的大部分資料列都會標示為「已處理」。 尚未處理的資料列篩選索引，可供正尋找符合此條件資料列的週期性查詢使用。
  • 當資料表具有異質資料列時。 您可以針對一或多個資料類別建立篩選索引。 這會將查詢焦點縮小為資料表的特定區域，改善這些資料列的查詢效能。 同樣地，所產生的索引比完整資料表非叢集索引還小，維護成本也比較低。

限制事項

  • 您無法在檢視上建立篩選索引。 不過，如果在檢視中參考的資料表上定義篩選索引，則可為查詢最佳化工具提供多項優點。 如果查詢結果會是正確的，則查詢最佳化工具會針對從檢視進行選取的查詢考慮篩選索引。

  • 當篩選運算式中存取的資料行為 CLR 資料類型時，您無法在資料表上建立篩選索引。

  • 篩選索引具有索引檢視表所不及的下列優勢：

    • 降低的索引維護成本。 例如，相較於索引檢視表而言，查詢處理器會使用較少的 CPU 資源來更新篩選索引。

    • 改善的計畫品質。 例如，在查詢編譯期間，查詢最佳化工具考慮使用篩選索引的情況會比對等的索引檢視表更多。

    • 線上索引重建。 您可以在篩選索引可用於查詢時，重建篩選索引。 線上索引重建不支援索引檢視表。 如需詳細資訊，請參閱 ALTER INDEX (Transact-SQL) 的 REBUILD 選項。

    • 非唯一索引。 篩選索引可以是非唯一的，而索引檢視表則必須是唯一的。

  • 篩選索引定義於單一資料表，且僅支援簡單比較運算子。 如果需要參考多個資料表或具有複雜邏輯的篩選運算式，則應該建立檢視。 篩選索引不支援 LIKE 運算子。

  • 如果篩選索引運算式相等於查詢述詞，且查詢並未以篩選索引運算式中的資料行傳回查詢結果，則篩選索引運算式中的資料行不需要是篩選索引定義中的索引鍵或內含資料行。

  • 如果查詢述詞在與篩選索引運算式相異的比較中，使用篩選索引運算式中的資料行，則該資料行應該是篩選索引定義中的索引鍵或內含資料行。

  • 如果篩選索引運算式中的資料行在查詢結果集中，則該資料行應該是篩選索引定義中的索引鍵或內含資料行。

  • 資料表的叢集索引鍵並不需要是篩選索引定義中的索引鍵或內含資料行。 叢集索引鍵會自動包含在所有非叢集的索引中 (包含篩選索引在內)。 如需詳細資訊，請參閱索引結構和設計指南。

  • 如果在篩選索引的篩選索引運算式中指定的比較運算子產生隱含或明確的資料轉換，則如果該轉換是發生在比較運算子的左側，就會發生錯誤。 解決方案是以資料轉換運算子 (CAST 或 CONVERT) 在比較運算子的右側寫入篩選索引運算式。

  • 檢閱 CREATE INDEX (Transact-SQL) 語法中用於建立篩選索引的必要 SET 選項

  • 篩選無法套用至主索引鍵或唯一條件約束，但可以套用至具有 UNIQUE 屬性的索引。

  • 您無法在計算資料行上建立篩選索引。

權限

需要資料表或檢視表的 ALTER 權限。 使用者必須是固定伺服器角色的 sysadmin 成員，或是固定資料庫角色的 db_ddladmin 和 db_owner。 若要修改篩選索引運算式，請使用 CREATE INDEX WITH DROP_EXISTING。

使用 SSMA 建立篩選索引

  1. 在物件總管中，選取加號來展開資料庫，此資料庫包含您要建立篩選索引的資料表。

  2. 選取加號展開 [資料表] 資料夾。

  3. 選取加號展開要建立篩選索引的資料表。

  4. 以滑鼠右鍵按一下 [索引] 資料夾，指向 [新增索引]，然後選取 [非叢集索引…]。

  5. 在 [新增索引] 對話方塊，於 [一般] 頁面上的 [索引名稱] 方塊中輸入新索引的名稱。

  6. 選取 [索引鍵資料行] 下的 [新增...]。

  7. 在 [從table_name選取數據行] 對話框中，選取要新增至索引之數據表數據行的複選框或複選框。

  8. 選取 [確定]。

  9. 在 [篩選] 頁面的 [篩選運算式]底下，輸入要用來建立篩選索引的 SQL 運算式。

  10. 選取 [確定]。

使用 Transact-SQL 建立篩選索引

本文 Transact-SQL 程式碼範例使用 AdventureWorks2022 或 AdventureWorksDW2022 範例資料庫，從 Microsoft SQL Server Samples 和 Community Projects （Microsoft SQL Server 範例和社群專案）首頁即可下載。

  1. 在物件總管中，連線到資料庫引擎的執行個體。

  2. 在標準列上，選取 [新增查詢]。

  3. 複製下列範例並將其貼到查詢視窗中，然後選取 [執行]。

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

篩選索引 FIBillOfMaterialsWithEndDate 對下列查詢有效。 您可以顯示查詢執行計畫，以判斷查詢最佳化工具是否已使用篩選索引。

USE AdventureWorks2022;
GO

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

下一步

若要深入瞭解如何建立索引和相關概念，請參閱下列文章：