五餅二魚工作室

參加一天的 SQL Pass , 透過一些大神們的分享 , 又偷學到幾招蠻不錯的技巧 , 要找時間來練習一下了。而在 Rico 兄的場子中在介紹一些 SQL Server 2022 的新函數，而其中一個就是所謂的 DATETRUNC 的功能，當時有朋友詢問到一個問題，如果使用到那個 DATETRUNC 的函數，這樣資料庫在查找資料的時候，會使用 Index 嗎 ?

基本上不管是 SQL Server 哪個版本，這個都算是 SARG 典型的問題，因此當下建議他使用計算欄位來搭配索引，但因為在會場中時間短暫，怕相關說明被誤會了，因此就整理了一下範例，或許可以表達得比較清楚一點

首先我先用類似以下的語法來建立一個範例資料表，主要會有一個 DATETIME 的欄位

CREATE TABLE T1 
(	F1	INT IDENTITY(1,1) PRIMARY KEY,
	F2	NVARCHAR(60)	DEFAULT NEWID(),
	F3	DATETIME
)

因為懶得打範例資料，因此用語法來產生， 基本上資料要有一定的筆數，才有辦法有效展示出來，所以用下面的語法跑了五次，產生 50000 筆的測試資料，這樣後續就可以透過執行計畫來看是否有使用 Index 了。

DECLARE @PTR INT = 1
BEGIN TRAN
WHILE @PTR <= 10000
BEGIN
	INSERT INTO T1(F3) VALUES ( DATEADD( MINUTE, RAND()*1000000 - 500000,  GETDATE() ));
	SET @PTR += 1;
END
COMMIT

首先先按照朋友當時的敘述，我先在 F3 的欄位上建立索引

CREATE NONCLUSTERED INDEX T1_F3 ON T1(F3)
GO

此時當我用以下的指令去找資料的時候

SELECT COUNT(*) FROM T1 where DATETRUNC(day, F3) = '2022/12/18'

透過執行計畫，可以看到因為搭配函數，因此這樣的指令雖然看起來好像有搭配索引，但要注意它是使用索引掃描，基本上索引並沒有辦法有效的來搭配函數來做使用。

---

雖然上述的範例中，看起來似乎有點令人沮喪，雖然 SQL 2022 提供一個比較便捷使用的函數，但似乎對我們的效能也沒有辦法改善，但針對這樣的方式，個人建議可以有三個方式來解決

A. 利用 DATETRUNC 產生計算欄位來搭配 Index

ALTER TABLE T1 ADD F4 AS DATETRUNC( day, F3) PERSISTED;
GO

CREATE NONCLUSTERED INDEX T1_F4 ON T1(F4)
GO

在上述的語法中我們產生一個計算欄位 F4，並且指定是 PERSISTED，這樣我們就可以針對那個欄位來建立索引，此時我們依然使用原本的測試指令

SELECT COUNT(*) FROM T1 where DATETRUNC(day, F3) = '2022/12/18'

從下圖中可以看出，雖然我們的語法沒有改變，但是 SQL Server 已經會改用 T1_F4 的索引來進行索引搜尋，這樣 Index 就可以配上用場了。

B. 利用 CAST 產生計算欄位來搭配 Index

ALTER TABLE T1 ADD F5 AS CAST( F3 AS DATE ) PERSISTED;
GO

CREATE NONCLUSTERED INDEX T1_F5 ON T1(F5)
GO

會這樣做的原因是很多人看了 SQL 2022 的 DATETRUNC，會覺得怎麼 SQL Server 到了 2022 才提供這樣的功能，那舊版的使用者，似乎就沒有辦法享受到這樣的功能。但其實不然，不可否認在 DATETRUNC 的確在使用上是非常有彈性，但如果只是要取得按照天來查看的時候，其實也可以直接把欄位轉為 DATE 的型態，這樣同樣的方式，當我們去查詢的時候，SQL Server 也是會使用索引來搭配使用，因此如果您是 SQL 2012 以後的版本，也都可以使用這樣的方式來處理了 ( date 型態我有點忘記是 2008 還是 2012 才開始提供 )

C. 直接改用區間的方式來查找資料

SELECT COUNT(*) FROM T1 where F3 >= '2022/12/18' AND F3 < '2022/12/19'

雖然採用計算欄位可以來用比較取巧的方式來使用索引，但有些時候可能系統有些時候會習慣使用 select * , 或者是可能因為一些沒有注意，造成因為有多增加計算欄位出問題，因此在不增加計算欄位的情況下，其實直接改語法去符合 SARG ，這樣也可以達到同樣的效果

---

因此想用這一篇跟今天與我討論的朋友分享一下，其實如果可以是話，我會建議使用 C 的方式，必不一定要使用 SQL Server 2022 所新增的函數，轉個想法其實問題可以更為簡單一點。