[SQL] 計算欄位 - where 條件

之前使用計算欄位來改善效能時，觀念上是建立計算欄位和索引後，where 搜尋條件也必須修改才會觸發計算欄位的 index seek，最近發現即使是維持原 where 條件也會觸發，該文章以 AdventrueWorks 2019 Person.Perosn Table 的 ModifiedDate 欄位來說明

下述 TSQL where 條件，對 ModifiedDate 欄位進行函數轉換，從執行計畫就可以觀察到跑 clustered index scan 

SELECT
  BusinessEntityID , 
  ModifiedDate
FROM Person.Person
WHERE (CAST(YEAR(ModifiedDate) AS varchar(4))
	+ RIGHT(REPLICATE('0' , 2) + CAST(MONTH(ModifiedDate) as varchar(2)) ,2))
	= '200901'

建立計算欄位 (YYYYMM) 和對應的 Index 來改善該情況

ALTER TABLE Person.Person ADD YYYYMM AS 
	(CAST(YEAR(ModifiedDate) AS varchar(4))
	+ RIGHT(REPLICATE('0' , 2) + CAST(MONTH(ModifiedDate) as varchar(2)) ,2))
	PERSISTED 
    
CREATE INDEX IX_Person_YYYYMM ON Person.Person (YYYYMM)

實際執行下述兩個 where 條件，都會觸發 IX_Person_YYYYMM index seek

-- 修正 where 條件，改以計算欄位為搜尋條件
SELECT
  BusinessEntityID , 
  ModifiedDate
FROM Person.Person
WHERE YYYYMM = '200901'

-- 維持原本 where 條件
SELECT
  BusinessEntityID , 
  ModifiedDate
FROM Person.Person
WHERE (CAST(YEAR(ModifiedDate) AS varchar(4))
	+ RIGHT(REPLICATE('0' , 2) + CAST(MONTH(ModifiedDate) as varchar(2)) ,2))
	= '200901'

最後來測試不同日期轉換方式，是否也能觸發 IX_Person_YYYYMM index seek

SELECT 
  BusinessEntityID , 
  ModifiedDate
FROM Person.Person
WHERE convert(varchar(6) , ModifiedDate , 112)
	= '200901'

從執行計畫就可以發現完全沒有使用 IX_Person_YYYYMM

基本上還是推薦 TSQL 語法也要一併改寫的作法

• 延伸閱讀
• [SQL] Like - 結尾字元搜尋改善
• [SQL] 避免欄位比較 - 計算欄位
• 參考資料
• [SQL]利用計算欄位搭配 Index 來提升效能
• Finding Long Values Faster: Answers & Discussion