篩選索引設計指導方針

篩選索引是最佳化的非叢集索引，特別適合涵蓋從妥善定義的資料子集進行選取的查詢。篩選索引會使用篩選述詞對資料表中的部分資料列進行索引。與完整資料表索引相較，設計良好的篩選索引可以提升查詢效能、降低索引維護成本，並降低索引儲存成本。

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

• 提升的查詢效能和計畫品質

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

• 降低的索引維護成本

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

• 降低的索引儲存成本

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

設計考量

為了設計有效的篩選索引，必須了解應用程式所使用的查詢以及這些查詢與資料子集的關聯。具有定義良好之子集的資料範例包括大部分的值都是 NULL 的資料行、具有異質值類別的資料行，以及具有相異值範圍的資料行。下列的設計考量提供多種案例，說明何時篩選索引的優點多於全資料表索引。

資料子集的篩選索引

當資料行僅具有少數的查詢相關值時，可以在值的子集上建立篩選索引。例如，當資料行中的值大部分都是 NULL 且查詢只會從非 NULL 值進行選取時，您可以針對非 NULL 的資料列建立篩選索引。所產生的索引比在相同的索引鍵資料行上定義的全資料表非叢集索引還小，維護成本也比較低。

例如，AdventureWorks2008R2 資料庫擁有 2679 個資料列的 Production.BillOfMaterials 資料表。EndDate 資料行僅擁有 199 個包含非 NULL 值的資料列，其他的 2480 個資料列都是包含 NULL。下列篩選索引所包含的查詢會傳回在索引中定義的資料行，並僅會選取 EndDate 使用非 NULL 值的資料列。

USE AdventureWorks2008R2;
GO
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
CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL ;
GO

篩選索引 FIBillOfMaterialsWithEndDate 對下列查詢有效。您可以顯示查詢執行計畫，以判斷查詢最佳化工具是否使用了篩選索引。如需有關顯示查詢執行計畫的詳細資訊，請參閱＜分析查詢＞。

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

如需有關如何建立篩選索引以及如何定義篩選索引述詞運算式的詳細資訊，請參閱＜CREATE INDEX (Transact-SQL)＞。

異質資料的篩選索引

當資料表具有異質資料列時，您可以針對一或多個資料類別建立篩選索引。

例如，列於 Production.Product 資料表中的每個產品都會被指派 ProductSubcategoryID，此 ID 又會與產品類別 Bikes、Components、Clothing 或 Accessories 等產生關聯。這些類別都是異質性的，因為它們在 Production.Product 資料表中的資料行值並沒有緊密關聯。例如，Color、ReorderPoint、ListPrice、Weight、Class 和 Style 等每個產品類別都具有唯一的特性。假設會經常查詢具有子類別 27-36 的 Accessories。您可以在 Accessories 子類別上建立篩選索引，以改善對 Accessories 查詢的效能。

下列範例會在 Production.Product 資料表中，Accessories 子類別的所有產品上建立篩選的索引。

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIProductAccessories'
    AND object_id = OBJECT_ID ('Production.Product'))
DROP INDEX FIProductAccessories
    ON Production.Product;
GO
CREATE NONCLUSTERED INDEX FIProductAccessories
    ON Production.Product (ProductSubcategoryID, ListPrice) 
        Include (Name)
WHERE ProductSubcategoryID >= 27 AND ProductSubcategoryID <= 36;
GO

篩選的索引 FIProductAccessories 涵蓋下列查詢，因為查詢

結果會包含在索引中，而且查詢計畫不包含基底資料表查閱。例如，查詢述詞運算式 ProductSubcategoryID = 33 是篩選之索引述詞 ProductSubcategoryID >= 27 的子集，而且查詢述詞中的 ProductSubcategoryID <= 36、ProductSubcategoryID 與 ListPrice 資料行都是索引中的索引鍵資料行，因此會將名稱當做包含的資料行，儲存在索引的分葉層級中。

SELECT Name, ProductSubcategoryID, ListPrice
FROM Production.Product
WHERE ProductSubcategoryID = 33 AND ListPrice > 25.00 ;
GO

檢視與篩選索引的比較

檢視是儲存查詢定義的虛擬資料表，其用途和功能都比篩選索引廣。如需檢視的詳細資訊，請參閱＜瞭解檢視＞和＜使用檢視的實例＞。下表比較在檢視以及篩選索引中所允許的一些功能。

運算式中是否允許	檢視	篩選索引
計算資料行	是	否
聯結	是	否
多個資料表	是	否
述詞中的簡單比較邏輯*	是	是
述詞中的複雜邏輯**	是	否

*如需述詞中簡單比較邏輯的資訊，請參閱 CREATE INDEX 中的 WHERE 子句語法。

**如需述詞中複雜比較邏輯的資訊，請參閱＜SELECT＞中的 WHERE 子句語法。

您無法在檢視上建立篩選索引；不過，如果在檢視中參考的資料表上定義篩選索引，則可為查詢最佳化工具提供多項優點。如果查詢結果會是正確的，則查詢最佳化工具會針對從檢視進行選取的查詢考慮篩選索引。下列範例建立開始日期在 2000 年 4 月 1 日以後的檢視以及開始日期在 2000 年 8 月 1 日以後的篩選索引。

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ('ViewOnBillOfMaterials') IS NOT NULL
DROP VIEW ViewOnBillOfMaterials;
GO
CREATE VIEW ViewOnBillOfMaterials AS 
SELECT ComponentID, StartDate, EndDate, StartDate + 2 AS ShipDate
FROM Production.BillOfMaterials
WHERE StartDate > '20000401';
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsByStartDate'
    AND object_ID = OBJECT_ID (N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsByStartDate 
    ON Production.BillOfMaterials;
GO
CREATE NONCLUSTERED INDEX FIBillOfMaterialsByStartDate
    ON Production.BillOfMaterials (ComponentID, StartDate, EndDate)
WHERE StartDate > '20000801';
GO

在下列範例中，查詢會選取 2004 年 9 月 1 日以後的開始日期，這些日期都會包含在篩選索引及篩選檢視中。查詢最佳化工具會考慮篩選索引 FIBillOfMaterialsByStartDate，因為它包含正確的查詢結果。

SELECT StartDate, ComponentID FROM ViewOnBillOfMaterials
WHERE StartDate > '20040901';
GO

在下一個範例中，查詢會選取 2004 年 6 月 1 日以後的開始日期，這些日期都會包含在檢視中，而不會包含在篩選索引中。查詢最佳化工具不會考慮篩選索引 FIBillOfMaterialsByStartDate，因為查詢可能會在查詢從檢視進行選取時，使用與正確結果相較的篩選索引以傳回不同的結果。

SELECT StartDate, ComponentID FROM ViewOnBillOfMaterials
WHERE StartDate > '20040601';
GO

索引檢視表與篩選索引

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

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

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

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

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

基於上述原因，我們建議您盡可能使用篩選索引而非索引檢視表。如果符合下列條件，您就可以使用篩選索引而非索引檢視表：檢視表僅參考一個資料表、查詢不會傳回計算資料行，而且檢視表述詞使用簡單的比較邏輯。例如，下列述詞運算式可用於檢視定義 (但不能用於篩選索引)，因為其包含 LIKE 運算子。

WHERE StartDate > '20040701' AND ModifiedDate LIKE 'E%'

索引鍵資料行

最佳作法是在篩選索引定義中包含少數的索引鍵或內含資料行，並僅併入查詢最佳化工具在選擇查詢執行計畫的篩選索引時必要的資料行。查詢最佳化工具可以選擇查詢的篩選索引，不論它是否涵蓋該查詢。然而，查詢最佳化工具如果涵蓋該查詢，則更可能選擇篩選索引。如需有關涵蓋查詢的詳細資訊，請參閱＜建立內含資料行的索引＞。

在某些情況下，篩選索引會涵蓋查詢，而不需將資料行以篩選索引定義中的索引鍵或內含資料行方式包含在篩選索引運算式中。下列指導方針說明篩選索引運算式中的資料行何時應該是篩選索引定義中的索引鍵或內含資料行。其中的範例會參考先前所建立的篩選索引 FIBillOfMaterialsWithEndDate。

如果篩選索引運算式相等於查詢述詞，且查詢並未以篩選索引運算式中的資料行傳回查詢結果，則篩選索引運算式中的資料行不需要是篩選索引定義中的索引鍵或內含資料行。例如，FIBillOfMaterialsWithEndDate 包含下列查詢，因為查詢述詞相等於篩選運算式，且查詢結果未傳回 EndDate。FIBillOfMaterialsWithEndDate 不需要在篩選索引定義中以 EndDate 做為索引鍵或內含資料行。

SELECT ComponentID, StartDate FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;
GO

如果查詢述詞在不等於篩選索引運算式的比較中使用篩選索引運算式中的資料行，則該資料行應該是篩選索引定義中的索引鍵或內含資料行。例如，FIBillOfMaterialsWithEndDate 對下列查詢有效，因為它會從篩選索引選取資料列子集；不過，它並不會涵蓋下列查詢，因為 EndDate 會用於比較 EndDate > '20040101' 中，而後者並不相等於篩選索引運算式。在未先查閱 EndDate 的值之前，查詢處理器無法執行此查詢。因此，EndDate 應該是篩選索引定義中的索引鍵或內含資料行。

SELECT ComponentID, StartDate FROM Production.BillOfMaterials
WHERE EndDate > '20040101';
GO

如果篩選索引運算式中的資料行在查詢結果集中，則該資料行應該是篩選索引定義中的索引鍵或內含資料行。例如，FIBillOfMaterialsWithEndDate 並不涵蓋下列查詢，因為它會在查詢結果中傳回 EndDate 資料行。因此，EndDate 應該是篩選索引定義中的索引鍵或內含資料行。

SELECT ComponentID, StartDate, EndDate FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;
GO

資料表的主索引鍵並不需要是篩選索引定義中的索引鍵或內含資料行。主索引鍵會自動包含在所有非叢集的索引中 (包含篩選索引在內)。

篩選述詞中的資料轉換運算子

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

下列範例會建立具有多種資料類型的資料表。

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ('dbo.TestTable') IS NOT NULL
DROP TABLE dbo.TestTable;
GO
CREATE TABLE TestTable (a int, b varbinary(4));
GO

在下列篩選索引定義中，資料行 b 會隱含地轉換成整數資料類型，以與常數 1 進行比較。這麼做會產生錯誤訊息 10611，因為轉換發生在篩選述詞中運算子的左側。

USE AdventureWorks2008R2;
GO
IF EXISTS ( SELECT name from sys.indexes 
    WHERE name = N'TestTabIndex'
    AND object_id = OBJECT_ID (N'dbo.TestTable'))
DROP INDEX TestTabIndex on dbo.TestTable
GO
CREATE NONCLUSTERED INDEX TestTabIndex ON dbo.TestTable(a,b)
WHERE b = 1;
GO

解決方案是將右側的常數轉譯為與資料行 b 相同的類型，如下列範例中所示：

CREATE INDEX TestTabIndex ON dbo.TestTable(a,b)
WHERE b = CONVERT(Varbinary(4), 1);
GO

將資料轉換從比較運算子的左側移至右側可能會變更轉換的意義。在上述範例中，當 CONVERT 運算子新增至右側時，比較作業會從整數比較變更為 varbinary 比較。

參考相依性

sys.sql_expression_dependencies 目錄檢視會將篩選索引運算式中的每個資料行當做參考相依性來追蹤。您不能卸除、重新命名或變更在篩選索引運算式中定義的資料表資料行定義。

何時使用篩選索引

當資料行包含定義良好的資料子集且查詢在 SELECT 陳述式中會參考這些資料時，篩選索引很有用。範例如下：

• 僅包含一些非 NULL 值的疏鬆資料行。

• 包含資料類別的異質資料行。

• 包含金額、時間和日期等值範圍的資料行。

• 由資料行值的簡單比較邏輯所定義的資料表資料分割。

當索引中的資料列數與全資料表索引相較比較小時，篩選索引降低的維護成本最明顯。如果篩選索引包含資料表中大部分的資料列，則其維護成本可能要比全資料表索引高。在此種情況下應該使用全資料表索引，而不是篩選索引。

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

篩選索引功能支援

一般而言，Database Engine 和工具為篩選索引所提供的支援與為非叢集的全資料表索引所提供的相同 (若將篩選索引視為特殊類型的非叢集索引)。下列清單提供有關完全支援、不支援或有限支援篩選索引之工具和功能的注意事項。

• ALTER INDEX 支援篩選索引。若要修改篩選索引運算式，請使用 CREATE INDEX WITH DROP_EXISTING。

• 遺漏的索引功能不支援建議篩選索引。

• Database Engine Tuning Advisor 在推薦索引微調建議時會考慮篩選的索引，而且建議使用 is not null 篩選的索引。

• 線上索引作業支援篩選的索引。

• 資料表提示支援篩選的索引，但具有某些無法套用至非篩選之索引的限制。這些將於下列章節中說明。

查詢考量

如果不論是否使用篩選索引，查詢都會選取相同的結果，則查詢最佳化工具可以使用篩選索引。先前所說明的篩選索引 FIBillOfMaterialsWithEndDate 對下列兩個查詢有效。在第一個範例中，查詢述詞與篩選索引述詞 WHERE EndDate IS NOT NULL 完全相符。在第二個範例中，查詢述詞則比篩選述詞更具有選擇性，因為它在索引中包含資料列子集。

SELECT ComponentID, StartDate FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;
GO
SELECT ComponentID, StartDate FROM Production.BillOfMaterials
WHERE EndDate < '20040701';
GO

下一個查詢也可以使用 FIBillOfMaterialsWithEndDate；不過，由於查詢述詞選擇性等決定查詢成本的其他因素，所以最佳化工具可能不會選取篩選索引。您可以使用篩選索引當做查詢提示，以強制最佳化工具選擇篩選索引，如下列範例所示。

SELECT ComponentID, StartDate FROM Production.BillOfMaterials
    WITH ( INDEX ( FIBillOfMaterialsWithEndDate ) )
WHERE EndDate IN ('20040825', '20040908', '20040918');
GO

如果查詢可以傳回不在篩選索引中的資料列，則查詢最佳化工具將不會使用篩選索引。例如，查詢最佳化工具不會考量將 FIBillOfMaterialsWithEndDate 用於下列查詢，因為查詢可能會傳回具有 NULL EndDate 及非 NULL ModifiedDate 的資料列，而 FIBillOfMaterialsWithEndDate 中不能有該資料列，因為它僅包含非 NULL 的 EndDate 值。

SELECT ComponentID, StartDate FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL OR ModifiedDate IS NOT NULL;
GO

如果明確使用篩選索引當做資料表提示，而篩選索引可能不包含所有的查詢結果，則查詢最佳化工具會產生查詢編譯錯誤 8622。在下列範例中，查詢最佳化工具會產生錯誤 8622，因為 FIBillOfMaterialsWithEndDate 對於查詢並非有效，且已明確用來當做索引提示：

SELECT StartDate, ComponentID FROM Production.BillOfMaterials
    WITH ( INDEX ( FIBillOfMaterialsWithEndDate ) )
WHERE EndDate IS NOT NULL OR ModifiedDate IS NOT NULL;
GO

參數化查詢

在某些情況下，參數化查詢在編譯階段並未包含足夠的資訊能讓查詢最佳化工具選擇篩選索引。可能可以重新撰寫查詢，以提供遺漏的資訊。在下列範例中，查詢最佳化工具不會在 SELECT 陳述式中考慮使用篩選索引 FIBillOfMaterialsWithComponentID，因為 @p 和 @q 的參數值在編譯階段是未知的。以下的查詢範例在執行時 SHOWPLAN_XML 會設定為 ON，如此您就可在 SHOWPLAN_XML 輸出中檢視參數化查詢不相符的篩選索引。

USE AdventureWorks2008R2;
GO
IF EXISTS ( SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsWithComponentID'
    AND object_id = OBJECT_ID (N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsWithComponentID
    ON Production.BillOfMaterials;
GO
CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithComponentID
    ON Production.BillOfMaterials (ComponentID, StartDate, EndDate)
WHERE ComponentID IN (533, 324, 753);
GO
SET SHOWPLAN_XML ON;
GO
DECLARE @p AS INT, @q AS INT;
SET @p = 533;
SET @q = 324;
SELECT StartDate, ComponentID from Production.BillOfMaterials 
WHERE ComponentID = @p OR ComponentID = @q;
GO
SET SHOWPLAN_XML OFF;
GO

SHOWPLAN_XML 輸出中的 UnmatchedIndexes 元素和 Parameterization 子元素指出篩選索引並非查詢的相符項目。如需有關如何檢視 SHOWPLAN_XML 輸出的詳細資訊，請參閱＜XML 顯示計畫＞。

解決方案是對查詢進行修改，使參數化運算式在並非篩選述詞的子集時，查詢結果會是空的。下列查詢示範此修改作業。藉由新增 ComponentID in (533, 324, 753) 運算式至 WHERE 子句，查詢的結果保證會是篩選述詞運算式的子集。在進行此項修改後，查詢最佳化工具可以考慮將篩選索引 FIBillOfMaterialsWithComponentID 用於下列的 SELECT 陳述式。

USE AdventureWorks2008R2;
GO
SET SHOWPLAN_XML ON;
GO
DECLARE @p AS INT, @q AS INT;
SET @p = 533;
SET @q = 324;
SELECT StartDate, ComponentID FROM Production.BillOfMaterials
WHERE ComponentID in (533, 324, 753)
    AND (ComponentID = @p OR ComponentID = @q);
GO
SET SHOWPLAN_XML OFF;
GO

簡單參數化

在大部分情況下，如果查詢計畫包含篩選的索引，查詢最佳化工具將不會針對查詢執行簡單參數化 (在 SQL Server 2005 中稱為「自動參數化」)。針對這類查詢執行簡單參數化可能會擴大可能參數值的範圍，導致篩選的索引無法確保查詢結果的精確度。例如，如果 SELECT 陳述式的 WHERE 子句使用了在篩選索引之述詞中使用的資料行，查詢最佳化工具可能就不會執行簡單參數化，因為查詢計畫可能包含篩選的索引。

在適當的情況下，您可以透過重寫查詢，將查詢參數化，並使用本節所描述的指導方針來確保篩選的索引將會涵蓋此查詢。

使用索引鍵查閱的查詢

查詢最佳化工具即使未涵蓋查詢，也可以執行索引鍵查閱以擷取篩選索引未涵蓋的剩餘資料行，藉以使用篩選的索引。如需有關索引鍵查閱的詳細資訊，請參閱＜Key Lookup Showplan 運算子＞。如果估計的索引鍵查閱數目不多，則查詢最佳化工具可能會選擇這種方式。下列查詢使用索引提示來強制查詢處理器藉由 EndDate 的書籤查閱使用 FIBillOfMaterialsWithEndDate。系統會針對查詢述詞中的 EndDate > @date 比較進行索引鍵查閱。

USE AdventureWorks2008R2;
GO
DECLARE @date AS DATE;
SET @date = '20040825'
SELECT ComponentID, StartDate, EndDate FROM Production.BillOfMaterials
WITH ( INDEX (FIBillOfMaterialsWithEndDate) )
WHERE EndDate > @date;
GO

請注意，EndDate > @Date 與篩選索引運算式 EndDate IS NOT NULL 並非完全相符。篩選索引對於此參數化查詢仍然有效，因為它會傳回由篩選索引運算式定義的資料列子集。

請參閱

參考

CREATE INDEX (Transact-SQL)

概念

使用統計資料來改善查詢效能