[SQL] Compare Operator - where Date Scalar Function

• 先前有介紹 Scalar Function ([SQL] Compare Operator - where String Scalar Function )
• sql 使用 where 下列使否影響查詢成本
• "日期純量函數" 與 "鍵值資料行"
• 比較運算子 ">", ">=", "<", "<=" 
• Between 
• 資料庫使用 Adventure Works for SQL Server 2012 的資料表 SalesOrderHeader

•  Step 1. 加入 NonClustered 包含 OrderDate 

• Step 2. 查詢 CASE 1

select SalesOrderID, OrderDate from Sales.SalesOrderHeader 
where Year(OrderDate) = 2005 and Month(OrderDate) = 8
select SalesOrderID, OrderDate from Sales.SalesOrderHeader 
where OrderDate >= '2005/08/01' and OrderDate < '2005/09/01'
select SalesOrderID, OrderDate from Sales.SalesOrderHeader 
where OrderDate between '2005/08/01' and '2005/08/31'

• Step 3. 查詢結果
• "非叢集索引" ncIndex 內包含自己的 "鍵值資料行" OrderDate 之外，也包含 "叢集索引" 的 "鍵值資料行" SalesOrderID
• 查詢 1. 使用 "日期函數" Year() 與 Month () 會使用到 "索引掃描"，因此成本會比較高
• 查詢 2, 3. 使用為 "索引搜尋"，效率高於 "索引掃描"，且查詢 2, 3的成本一樣

• Step 4. 查詢 CASE 2：輸出資料行 DueDate 不包含於 "非叢集索引" ncIndex 內

select SalesOrderID, OrderDate, DueDate from Sales.SalesOrderHeader 
where Year(OrderDate) = 2005 and Month(OrderDate) = 8
select SalesOrderID, OrderDate, DueDate from Sales.SalesOrderHeader 
where OrderDate >= '2005/08/01' and OrderDate < '2005/09/01'

• Step 5. 查詢結果
• 因多個資料行 DueDate
• 查詢 1. 使用 "索引掃描" 外還使用 "索引鍵查詢" (Clustered) 和 "巢狀迴圈"
• 查詢 2. "索引搜尋" 變為 "叢集索引掃描"，因而增加許多的成本

• Step 6. 查詢 CASE 3：將 DueDate 加入 "非叢集索引" ncIndex

select SalesOrderID, OrderDate, DueDate from Sales.SalesOrderHeader 
where Year(OrderDate) = 2005 and Month(OrderDate) = 8
select SalesOrderID, OrderDate, DueDate from Sales.SalesOrderHeader 
where OrderDate >= '2005/08/01' and OrderDate < '2005/09/01'

• Step 7. 查詢結果
• 結果與 CASE 1 結果一樣，輸出資訊是否有在 "非叢集索引"，是有相關性

• Result：
1. 使用 "日期函數" Year() 與 Month () 的查詢，只能使用 "掃描" 無法使用 "搜尋"， 而 "鍵值資料行" 經過函數運算，就不能使用 "搜尋"
2. "搜尋" 查詢成本相對 "掃描" 非常多，因 "查閱" 不同會使得成本更高
3. "非叢集資料行存放區索引" (NonClustered ColumnStore Index) 優點為降低 "搜尋" 成本，且將高成本的 "查閱" 摒除
4. 但 "非叢集資料行存放區索引" (NonClustered ColumnStore Index) 缺點增加硬碟使用空間，而引響 "新增，修改與刪除" 效能
• REF：
• SQL Server 效能調校