篩選索引設計指導方針

當資料行僅具有少數的查詢相關值時，可以在值的子集上建立篩選索引。例如，當資料行中的值大部分都是 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 篩選的索引。

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

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

在某些情況下，參數化查詢在編譯階段並未包含足夠的資訊能讓查詢最佳化工具選擇篩選索引。可能可以重新撰寫查詢，以提供遺漏的資訊。在下列範例中，查詢最佳化工具不會在 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

簡單參數化

查詢最佳化工具即使未涵蓋查詢，也可以執行索引鍵查閱以擷取篩選索引未涵蓋的剩餘資料行，藉以使用篩選的索引。如需有關索引鍵查閱的詳細資訊，請參閱＜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 並非完全相符。篩選索引對於此參數化查詢仍然有效，因為它會傳回由篩選索引運算式定義的資料列子集。