[SQL] Compare Operator - Filtered Indexes

• "Filtered Indexes" 與 一般索引在成本上的差異
• 資料庫使用 Adventure Works for SQL Server 2012 的資料表 Sales.SalesOrderHeader 產生 1 個 table 為 tbSalesOrderHeader  
• NonClustered Index：SalesOrderID, PurchaseOrderNumber
• NonClustered ColumnStore  Index：OrderDate, ShipDate, ModifiedDate

• CASE 1：
• 查詢

select SalesOrderID, PurchaseOrderNumber, OrderDate, ShipDate, ModifiedDate 
from tbSalesOrderHeader
where PurchaseOrderNumber like 'PO6%'

select SalesOrderID, PurchaseOrderNumber, OrderDate, ShipDate, ModifiedDate 
from tbSalesOrderHeader
where PurchaseOrderNumber like 'PO6%'
 and ModifiedDate >= '2008/01/01'
 and SalesOrderID is not null

• 查詢結果
• 查詢 1, 2 為相同成本

• CASE 2：
• 建立 filtered index

• 查詢語法相同 CASE 1

select SalesOrderID, PurchaseOrderNumber, OrderDate, ShipDate, ModifiedDate 
from tbSalesOrderHeader
where PurchaseOrderNumber like 'PO6%'

select SalesOrderID, PurchaseOrderNumber, OrderDate, ShipDate, ModifiedDate 
from tbSalesOrderHeader
where PurchaseOrderNumber like 'PO6%'
 and ModifiedDate >= '2008/01/01'
 and SalesOrderID is not null

• 查詢結果
• 查詢 1：因不符合 "Filtered Indexes" 條件，所以無法使用此索引，採取資料掃描
• 查詢 2：因符合 "Filtered Indexes" 條件，所以使用 "Filtered Indexes"

• CASE 3：
• 比較 CASE 1 與 CASE 2 的成本
• CASE 2 加入 "Filtered Indexes" ，因  "Filtered Indexes" 將資料篩選，所以資料會比較小，索引成本會比較低

• Result：
1. "Filtered Indexes" 經過篩選，所以 "索引" 資料列必定比原先資料列少，"搜尋"成本也比較低
2. "Filtered Indexes" 當 where 條件不符合，系統不會使用該 "Filtered Indexes"，因此還是可以查尋其它資料列
3. "Filtered Indexes" 的 where 條件沒規定一定要 "鍵值資料行"，"非鍵值資料行" 也可成為篩選條件
4. 使用時機：當資料表中大部份都屬於歷史資料，如去年資料，因此可建立一個屬於本年度 "篩選索引"，這樣的索引就可以不用查尋去年之前的資訊
• REF：
• SQL Server 效能調校