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

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

建立 DoneQty 欄位、更新資料並建立 IX_Transport_DoneQty Index

ALTER TABLE Transport ADD DoneQty int NOT NULL DEFAULT(0)
CREATE INDEX IX_Transport_DoneQty ON Transport (DoneQty)

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

-- 欄位資料 運算子 欄位資料
SELECT * FROM Transport WHERE Qty > DoneQty
-- 對欄位進行運算
SELECT * FROM Transport WHERE Qty - DoneQty > 0

觀察執行計劃就會發現，上述兩種寫法，因為違反 SARG，所以根本就不會使用 IX_Transport_DoneQty，通通跑 Clustered Index Scan

強制指定 Index 來看看結果

SELECT * FROM Transport WHERE Qty > DoneQty
SELECT * FROM Transport WITH (INDEX(IX_Transport_DoneQty)) WHERE Qty > DoneQty

結果當然是更慘囉

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

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

ALTER TABLE Transport ADD ComputeCol AS (Qty -DoneQty) PERSISTED 
CREATE INDEX IX_Transport_ComputeCol ON Transport (ComputeCol)

原語法和計算欄位較

SELECT * FROM Transport WHERE Qty > DoneQty
SELECT * FROM Transport WHERE ComputeCol > 0

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

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