五餅二魚工作室

以往架設的一個小知識庫這幾天要被清掉了，因此這幾天在整理幾個之前處理的案例，把這些移到點部落上面，雖然之前大鈞開玩笑說要關站，但我想短時間內他應該不會那麼殘忍吧 :)

言歸正傳，回到我們的主題。一般來說如果我們需要針對資料做起訖的查詢時，如果只有一個欄位，那麼直接使用 between 的指令就可以了；但有些時候我們需要兩個欄位( EX : 單別+單號, 部門代號+員工代號等 ) 的時候，那麼就很容易會有以下這樣的 SQL 語法出現 ( PS. 該範例因為 TC001 欄位是 char(4)，所以資料之間會有放入一個空白 )

SELECT * FROM PURTC
  WHERE ( TC001+TC002 >= '330 1007010' ) AND ( TC001+TC002 <='330 1009055' )

這樣只雖然很容易看得懂，但是因為無法配合到資料庫的 Index，因此會造成 Table Scan or Index Scan，使得整體的效能不好，因此當初在協助查看問題的時候，就建議開發人員做一些調整，可以將 SQL 改成以下的方式：

SELECT * FROM PURTC
  WHERE (( TC001='330 ' AND TC002 >= '1007010' ) )
   AND  (( TC001='330 ' AND TC002 <= '1009055' ) )

或者是

SELECT * FROM PURTC
  WHERE TC001='330 ' AND TC002 BETWEEN  '1007010' AND '1009055' 

此時如果該 TABLE 有配合 TC001 和 TC002 去建立 Index 的環境下，則 SQL Server 就會改成利用 Index Seek 的方式去搜尋資料。這樣調整之後，比對一下兩者的效果，所查詢出來的資料數量和修改前是一樣的，但總執行時間上有顯著的差異。

但開發人員看了之後，卻表示沒有辦法這樣的修改，因為雖然部分查詢的時候，起訖資料的第一個欄位多半都會相同，但如果第一個欄位不同的時候，則上述的修改方式可能會造成有部分資料會取不到，舉個例子來說，原本的 SQL 可能會類似以下的寫法：

SELECT * FROM PURTC
  WHERE ( TC001+TC002 >= '330 20081101001' ) AND ( TC001+TC002 <='339 20081205001' )

那如果改成以下的寫法 :

SELECT * FROM PURTC
  WHERE ( TC001 = '330 ' AND TC002 >= '20081101001' ) 
    AND ( TC001 = '339 ' AND TC002 <= '20081205001' )

就有可能會發生 TC001 落在 '330 ' & '339 ' 之間的資料取不到，因此這個時候就要再調整一下，變成如下的語法：

SELECT * FROM PURTC
  WHERE (( TC001='330 ' AND TC002 >= '20081101001' ) OR ( TC001>'330 ' ))
   AND  (( TC001='339 ' AND TC002 <= '20081205001' ) OR ( TC001<'339 ' ))

原本以為這樣就可以解決了，但程式開發人員因為要改的地方實在太多了，短時間內開發人員並沒有辦法一次修改完畢。因此開發單位主管希望找到一個暫時解法，能夠先做局部改善，再來慢慢修改程式，再跟相關人員討論完畢之後，於是建議他們可以先暫時用計算欄位 ( computed column ) 來作解決，因此會先針對原始 Table 做一些調整：

ALTER TABLE dbo.PURTC ADD
    X  AS TC001+TC002 PERSISTED 

透過上述的指令，我們產生一個計算欄位 X，並且指定是 PERSISTED 的，因此我們就可以針對這個欄位來建立 INDEX

CREATE NONCLUSTERED INDEX IX_PURTC 
  ON dbo.PURTC(X)  ON [PRIMARY]

這要即便我們沒有改 SQL 指令，仍然會透過 INDEX 去搜尋，只是相對地來說，原本的 TABLE 會占用比較多的空間去儲存資料，而也要另外多建立一組 INDEX 來儲存，這些都是我們在使用時需要考量的因素。除此之外，也不是所有狀況下都可以這樣使用的，必須要注意原本系統是否有採用 SELECT * 的方式去取資料，這樣可能會多出一些欄位，還有就是當使用 INSERT 指令的時候，也要指定欄位，且不能針對這個計算欄位做處理。