在使用 WHERE 搜尋條件 去篩選 日期時間 資料型態，建議：
- 使用 比較運算子(Comparison Operators) 來篩選出明確的日期時間範圍
- 例如：>= (大於或等於), <= (小於或等於), < (小於)。
- 避免使用 CONVERT/CAST 或 BETWEEN。
目的是：
- 符合 搜尋引數(Search Arguments, SARGs) ，效能優化。
- 資料正確性，沒有因 不正確的假設導致不正確的結果。
建議用法：
- 當 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 資料轉型為 字串資料類型後做比對：
- 是可以正確搜尋到 3 筆資料列。
- 但這是 違反符合 SARGs，導致 無法使用到索引。
- 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
