[SQL Server] WHERE search condition with date: Use a range of dates and don't use CONVERT/CAST or BETWEEN (1)

在使用 WHERE 搜尋條件 去篩選 日期時間 資料型態，建議：

1. 使用 比較運算子(Comparison Operators) 來篩選出明確的日期時間範圍
• 例如：>= (大於或等於),  <= (小於或等於), < (小於)。
2. 避免使用 CONVERT/CAST 或 BETWEEN。

目的是：

1. 符合 搜尋引數(Search Arguments, SARGs) ，效能優化。
2. 資料正確性，沒有因 不正確的假設導致不正確的結果。

建議用法：

• 當 WHERE 搜尋條件 去篩選 日期時間 資料型態時
• 使用 比較運算(Comparison Operators) 來篩選出明確的日期時間範圍
• 例如：>= (大於或等於),  <= (小於或等於), < (小於)。

-- 使用 比較運算子(Comparison Operators) 來篩選出明確的日期時間範圍
-- 例如：>= (大於或等於),  <= (小於或等於), < (小於)
-- 需求：篩選 20140805 當日的資料，回傳 3 rows
SELECT TransactionID, ProductID, Quantity, TransactionDate 
FROM dbo.Transactions02
WHERE TransactionDate >= '20140805' AND TransactionDate<'20140806'

使用 比較運算子，符合 SARG，使用到索引
無須額外耗用 CONVERT/CAST 轉型

WHERE TransactionDate >= '20140805' AND TransactionDate < '20140806'

對照 Subtree Cost(子樹成本)

語法	Subtree Cost(子樹成本)
使用 比較運算子，符合 SARG，使用到索引	0.104689
額外使用 CONVERT/CAST	0.722386

可以看到明顯相差達 7 倍。

---

資料表 Transactions01 的 TransactionDate 沒有索引

--01. 檢視資料特性_Datetime

TransactionDate 資料行的資料類型是：Datetime

若需求是 篩選出 20140805 當日 的交易資訊，預期應該有 3 筆資料列符合回傳。

但是 WHERE 條件式寫成：WHERE TransactionDate = '20140805'，省略了時間值，這是篩選出 2014-08-05 00:00:00:000 時間點的資料值。
因此，僅回傳 1 筆資料列。

-- 02_只用等號_卻是僅回傳 1 row

若 額外 再去使用 CONVERT 或 CAST 將 DateTime 資料轉型為 字串資料類型後做比對：

1. 是可以正確搜尋到 3 筆資料列。
2. 但這是 違反符合 SARGs，導致 無法使用到索引。
3. SQL Server Query Engine 也因為此資料類型的轉換，進而影響到 查詢計劃選擇中的 "CardinalityEstimate" 或 "SeekPlan"。

WHERE CONVERT(varchar(30),TransactionDate, 112) = '20140805'

-- 03_CONVERT 轉換 DateTime 資料型別 為 字串

觀察 Execution Plan，可以看到特別顯示 驚嘆號!

-- 04_Execution_Plan_issue

執行計畫內的警告訊息：
警告：

• 運算式 (CONVERT(varchar(30),[tempdb].[dbo].[Transactions01].[TransactionDate],112)) 中的類型轉換可能會影響到查詢計劃選擇中的 "CardinalityEstimate", 
• 運算式 (CONVERT(varchar(30),[tempdb].[dbo].[Transactions01].[TransactionDate],112)=[@1]) 中的類型轉換可能會影響到查詢計劃選擇中的 "SeekPlan"

-- 05_Execution_Plan_issue

-- 06_Execution_Plan_issue

使用 SentryOne Plan Explorer，亦可觀察到 Warnings 訊息。

-- 07_Execution_Plan_issue

建議用法

• 使用 比較運算(Comparison Operators) 來篩選出明確的日期時間範圍
• 例如：>= (大於或等於),  <= (小於或等於), < (小於)

WHERE TransactionDate >= '20140805' AND TransactionDate < '20140806'

-- 08_沒有警告訊息的Exection_Plan

-- 09_沒有警告訊息的Exection_Plan

---

使用「搜尋引數(Search Argument,SARG)」

資料表 Transactions02 的 TransactionDate 有索引

-- 10_資料表 Transactions02 的 TransactionDate 有索引

不符合 SARG 規範，導致無法使用索引。
WHERE CONVERT(varchar(30),TransactionDate, 112) = '20140805'

-- 11_Non SARG 01

-- 12_Non SARG 01_Execution_Plan

不符合 SARG 規範，導致無法使用索引
WHERE DATEPART(yyyy,TransactionDate)=2014 AND DATEPART(mm,TransactionDate)=8 AND DATEPART(dd,TransactionDate) = 5

-- 13_Non SARG 02

-- 14_Non SARG 02_Execution_Plan

建議語法：符合 SARG，可以使用到索引

WHERE TransactionDate >= '20140805' AND TransactionDate < '20140806'

-- 015_SARG_Index

-- 016_SARG_Index_Execution_Plan

---

範例程式碼

20171014_WHERE_DATE_Compare_Operator
https://drive.google.com/drive/folders/0B9PQZW3M2F40MDNoMGF4Wml6M00?usp=sharing

---

參考資料

What do BETWEEN and the devil have in common?
http://sqlblog.com/blogs/aaron_bertrand/archive/2011/10/19/what-do-between-and-the-devil-have-in-common.aspx

It's Time for Time
http://www.kimballgroup.com/1997/07/its-time-for-time/

INF: Search Arguments That Determine Distribution Page Usage
http://support.microsoft.com/kb/169642/en-us

SQL Server 2005 Compact Edition Books Online
Query Performance Tuning (SQL Server Compact Edition)
http://msdn.microsoft.com/en-us/library/ms172984.aspx

Comparison Operators (Transact-SQL)
https://technet.microsoft.com/EN-US/library/ms188074(v=sql.105).aspx

SentryOne Plan Explorer | SQL Query Optimization
https://www.sentryone.com/plan-explorer

[SQL Server] WHERE search condition with date: Use a range of dates and don't use CONVERT/CAST or BETWEEN (2)
http://sharedderrick.blogspot.tw/2017/10/sql-server-where-search-condition-with_15.html