搜尋本站文章

2017-10-15

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


延續前一篇的討論
[SQL Server] WHERE search condition with date: Use a range of dates and don't use CONVERT/CAST or BETWEEN (1)


建議 使用 比較運算子 + 邏輯運算子

  1. 使用 比較運算子 + 邏輯運算子,符合 SARG,使用到索引
  2. 無須額外耗用 CONVERT/CAST 轉換資料類型
  3. 直觀的語法、語意,通用性高

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



為何不建議使用  BETWEEN ?

因為 BETWEEN 在處理 DATE/TIME 資料類型時,容易造成誤解,導致查詢結果不正確。

問題:以下  WHERE 搜尋條件 是找出什麼資料?

WHERE TransactionDate BETWEEN '20140805' AND '20140806'

答案會是?

  1. 20140805 當日的全部資料
  2. 20140805 ~ 20140806 兩日的全部資料
  3. 20140805 全日 加上 20140806 00:00:00:000 時間點的資料


用以下範例來討論

-- 01_檢視資料特性_Datetime



WHERE TransactionDate BETWEEN '20140805' AND '20140806'

卻是篩選出 20140805 當日 加上 20140806 00:00:00:000 時間點的資料
回傳:4 筆資料列,Cost:0.150749

-- 02_BETWEEN_僅回傳4_rows


預期

應該回傳 5 筆資料列,卻僅回傳 4 筆資料列,缺少 1 筆資料列:2014-08-06 07:00:00.000

以條件式 BETWEEN '20140805' AND '20140806' 為例
多半寫出這類的語句,應該要找的是包含這 兩天 的全部資料
預期應該回傳 5 筆資料列。

卻是

但實際上
BETWEEN '20140805 00:00:00.000' AND '20140806 00:00:00.000'

也就是:20140805 全日 加上 20140806 00:00:00:000 時間點的資料。並未包含 20140806 當日的全部資料。

解決方案

該如何有效解決問題呢?

  • 改用 比較運算子 + 邏輯運算子 來篩選出明確的日期時間範圍
    • 例如:>= (大於或等於),  <= (小於或等於), < (小於)。


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

-- 03_改用比較運算子_正確回傳5_rows





對照執行計畫

近一步觀察執行計畫,以 BETWEEN 比較運算子 +邏輯運算子 來對照,BETWEEN 是耗用更多的資源。

-- 10_對照兩者執行計畫


-- 11_BETWEEN_搜尋述詞


觀察

  • 搜尋述詞-起點
    • [tempdb].[dbo].[Transactions02].TransactionDate >= 純量運算子('2014-08-05 00:00:00.000')
  • 搜尋述詞-終點 
    • [tempdb].[dbo].[Transactions02].TransactionDate <= 純量運算子('2014-08-06 00:00:00.000')

留意

BETWEEN 的終點,卻是被解譯為:
<= 純量運算子('2014-08-06 00:00:00.000')

-- 12_BETWEEN_搜尋述詞_2


-- 13_BETWEEN_搜尋述詞_3



使用  比較運算子 + 邏輯運算子

如預期的結果正確回傳。
語意沒有被誤解。

-- 14_比較運算子_搜尋述詞



-- 15_比較運算子_搜尋述詞_2


-- 16_比較運算子_搜尋述詞_3




範例程式碼

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



參考資料

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

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/








沒有留言:

張貼留言