[SQL] Compare Operator - Composite Indexes

• 瞭解 "Composite Indexes" 使用時機與建立原則
• 資料庫使用 Adventure Works for SQL Server 2012 的資料表 Production.Product 產生 1 個資料表，但包含 "Clustered" 與多個 "Composite Indexes"
• clIndex：Clustered (BusinessEntityID)
• ncIndex01：NonClustered Index (FirstName)
• ncIndex02：NonClustered Index (FirstName, LastName)
• ncIndex03：NonClustered Index (FirstName, LastName, PersonType)

• CASE 1. 
• 查尋

select BusinessEntityID, FirstName, LastName 
from tbPerson
where FirstName = 'Mae'

select BusinessEntityID, FirstName, LastName 
from tbPerson
where FirstName = 'Mae' and LastName = 'Black'

select BusinessEntityID, FirstName, LastName from tbPerson
where FirstName = 'Mae' and LastName = 'Black' and PersonType = 'SC'

• 查詢結果
• 因 where 條件分別使用到 (FirstName), (FirstName, LastName), (FirstName, LastName, PersonType)，結果 Query Optimier 皆採用 ncIndex03
• ncIndex03 索引同時符合此三個查詢 "索引搜尋" 需求
• ncIndex03 索引同時符合此三個查詢所需的資料行，因此採用 ncIndex03 索引成本將是最低

• CASE 1. 
• 查尋

select BusinessEntityID, FirstName, LastName 
from tbPerson
where FirstName = 'Mae'

select BusinessEntityID, FirstName, LastName 
from tbPerson with(index(ncIndex01))
where FirstName = 'Mae'

select BusinessEntityID, FirstName, LastName, Title 
from tbPerson
where FirstName = 'Mae' and LastName = 'Black' and PersonType = 'SC'

select BusinessEntityID, FirstName, LastName 
from tbPerson
where LastName = 'Black' and PersonType = 'SC'

• 查詢結果
• 查詢2：強制使用 ncIndex01 查詢效能上反而慢
• 查詢3：當資料行不在 ncIndex 中，將會使用 clIndex 將再 "內部連結" 合併
• 查詢4：條件未使用 FirstName，使 "搜尋" 變為 "掃描"，因而效能最差

• Result：
1. 如果常用查詢的 where 條件中有共用資料行，可建立 "Composite Indexes"，依據使用頻率高低指定 "鍵值資料行"，常出現的共同資料行排在前面，不常出現資料行排在後面
2. "Composite Indexes" 原則，"鍵值資料行" 中的第一個 "資料行" 一定要在 where 條件中租線，否則無法使用 "搜尋"
3. "Composite Indexes" (ncIndex03)，可同時取代數個 "索引" (ncIndex01 與 ncIndex02)
• REF：
• SQL Server 效能調校