[SQL] 避免欄位比較 - 計算欄位

在 [SQL] 避免欄位比較 - 篩選索引 這篇筆記內，是建立一個待運送 (剩餘數量) 欄位來改善，這篇筆記就改為建立 [已運輸數量欄位] 來筆記囉

建立已運輸數量欄位、更新資料並建立 IX_物流申請_已運輸數量 Index

ALTER TABLE 物流申請 ADD 已運輸數量 int NOT NULL DEFAULT(0)
GO

CREATE INDEX IX_物流申請_已運輸數量 ON 物流申請 (已運輸數量)
GO

要在已運送數量欄位形式，去找出待運送數量資訊，就只有下面兩種不符合 SARG 寫法

-- 欄位資料 運算子 欄位資料
SELECT * FROM 物流申請 WHERE 申請運輸數量 > 已運輸數量

-- 對欄位進行運算
SELECT * FROM 物流申請 WHERE 申請運輸數量 - 已運輸數量 > 0

觀察執行計劃就會發現，上述兩種寫法，因為違反 SARG，所以根本就不會使用 IX_物流申請_已運輸數量，通通跑 Clustered Index Scan

強制指定 Index 來看看結果

SELECT * FROM 物流申請 WHERE 申請運輸數量 > 已運輸數量

SELECT * FROM 物流申請 WITH (INDEX(IX_物流申請_已運輸數量)) WHERE 申請運輸數量 > 已運輸數量

結果當然是更慘囉

原本是要避免 [資料欄位 運算子 資料欄位]，這種違反 SARG 的設計，沒想到因為是建立並儲存 [已運送數量] 欄位，仍然是陷入同樣問題，在木已成舟情況下，要使用符合 SARG 語法並找出 [待運送數量] 資料，還能透過 [計算欄位] 來達到

建立計算欄位並建立計算 Index

ALTER TABLE 物流申請 ADD ComputeCol AS (申請運輸數量 - 已運輸數量) PERSISTED 

CREATE INDEX IX_物流單_ComputeCol ON 物流申請 (ComputeCol)

原語法和計算欄位較

SELECT * FROM 物流申請 WHERE 申請運輸數量 > 已運輸數量

SELECT * FROM 物流申請 WHERE ComputeCol > 0

公司生管派工系統，彼此之間會已完工數量來溝通，基本上不會有人跟下製程說，還剩下多少數量沒有完成，而是告知已完成數量，也因此在 Table 設計時，各製程是儲存已完工數量資訊，在判斷製程完工時也會遇上該問題，剛好整理一下，以後這類系統發生問題時，至少有個明確方向可以來改善

• 參考資料
• [SQL]利用計算欄位搭配 Index 來提升效能