SQL Server 篩選索引深度解析：建立條件、INCLUDE 結合、限制與參數化陷阱

篩選索引 只索引符合 WHERE 條件的子集資料列，減少索引體積、維護成本，提升特定查詢效能。本文全面解析建立語法、應用場景、限制與變數參數化陷阱。

語法格式

CREATE NONCLUSTERED INDEX idx_name ON table(columns) 
WHERE filter_predicate;

案例一：查詢條件欄位建立篩選索引

-- 基礎非叢集索引
CREATE NONCLUSTERED INDEX IX_UnitPrice ON Sales.SalesOrderDetail(UnitPrice);

-- 篩選索引：僅索引 UnitPrice > 1000 的列
CREATE NONCLUSTERED INDEX IX_UnitPrice_Filtered ON Sales.SalesOrderDetail(UnitPrice) 
WHERE UnitPrice > 1000;

查詢 WHERE UnitPrice > 2000 時，篩選索引開銷較基礎索引再降 ~50%。

案例二：唯一篩選索引（排除 NULL）

CREATE UNIQUE NONCLUSTERED INDEX UQ_Email_NotNull ON Customers(Email) 
WHERE Email IS NOT NULL;

允許單一 NULL，其餘值強制唯一，解決「多 NULL 違反唯一限制」問題。

案例三：結合 INCLUDE 移除 Key Lookup

CREATE NONCLUSTERED INDEX IX_Filtered_Include ON Sales.SalesOrderDetail(UnitPrice) 
INCLUDE(UnitPriceDiscount) WHERE UnitPrice > 2000;

查詢含 UnitPriceDiscount 時同樣避免 Lookup，索引更精簡。

案例四：字串欄位超長無法建索引 → 改建主鍵篩選索引

• SomeValue VARCHAR(MAX) 超過 900 位元組上限
• 改在叢集索引鍵 RowID 建篩選索引：WHERE SomeValue = 'JeffckyWang'
• 再加 INCLUDE(SomeValue, StartDate) 覆蓋查詢

篩選索引優缺點總結

參數化查詢陷阱與解法

變數比較 WHERE ProductID = @pid 不會自動使用篩選索引。

-- 需加 OPTION (RECOMPILE) 讓優化器嗅探參數值
SELECT ProductID FROM Sales.SalesOrderDetail 
WHERE ProductID = @pid OPTION (RECOMPILE);

或改用動態 SQL、字面常數查詢。

適用場景建議

• 高度傾斜資料：如 99% 訂單 Status = 'Completed'，只索引 1% 異常狀態
• 歷史資料歸檔：WHERE CreatedDate < '2020-01-01'
• 稀疏欄位：WHERE SparseCol IS NOT NULL

善用篩選索引，能以最小成本換取精準查詢加速。