SQL Server中的臨時表和表變數

在SQL Server的效能調優中，有一個不可比面的問題：那就是如何在一段需要長時間的程式碼或被頻繁呼叫的程式碼中處理臨時資料集？表變數和臨時表是兩種選擇。記得在給一家國內首屈一指的海運公司作SQL Server應用效能評估和調優的時候就看到過大量的臨時資料集處理需求，而他們的開發人員就無法確定什麼時候用臨時表，什麼時候用表變數，因此他們就簡單的使用了臨時表。實際上臨時表和表變數都有特定的適用環境。

先賣弄一些基礎的知識：

表變數

變數都以@或@@為字首，表變數是變數的一種，另外一種變數被稱為標量(可以理解為標準變數，就是標準資料型別的變數，例如整型int或者日期型DateTime)。以@字首的表變數是本地的，因此只有在當前使用者會話中才可以訪問，而@@字首的表變數是全域性的，通常都是系統變數，比如說@@error代表最近的一個T-SQL語句的報錯號。當然因為表變數首先是個變數，因此它只能在一個Batch中生存，也就是我們所說的邊界，超出了這個邊界，表變數也就消亡了。

表變數存放在記憶體中，正是因為這一點所有使用者訪問表變數的時候SQL Server是不需要生成日誌。同時變數是不需要考慮其他會話訪問的問題，因此也不需要鎖機制，對於非常繁忙的系統來說，避免鎖的使用可以減少一部分系統負載。

表變數另外還有一個限制就是不能建立索引，當然也不存在統計資料的問題，因此在使用者訪問表變數的時候也就不存在執行計劃選擇的問題了(也就是以為著編譯階段後就沒有優化階段了)，這一特性有的時候是件好事，而有些時候卻會造成一些麻煩。

臨時表

臨時物件都以#或##為字首，臨時表是臨時物件的一種，還有例如臨時儲存過程、臨時函式之類的臨時物件，臨時物件都儲存在tempdb中。以#字首的臨時表為本地的，因此只有在當前使用者會話中才可以訪問，而##字首的臨時表是全域性的，因此所有使用者會話都可以訪問。臨時表以會話為邊界，只要建立臨時表的會話沒有結束，臨時表就會持續存在，當然使用者在會話中可以通過DROP TABLE命令提前銷燬臨時表。

我們前面說過臨時表儲存在tempdb中，因此臨時表的訪問是有可能造成物理IO的，當然在修改時也需要生成日誌來確保一致性，同時鎖機制也是不可缺少的。

跟表變數另外一個顯著去別就是臨時表可以建立索引，也可以定義統計資料，因此SQL Server在處理訪問臨時表的語句時需要考慮執行計劃優化的問題。

表變數 vs. 臨時表

	表變數	臨時表
資料集的儲存位置	記憶體(不考慮被換到頁面檔案這種情況)	磁碟(不考慮訪問後被快取到記憶體中)
是否需要日誌	否	是
是否可以建立索引	否	是
是否可以使用統計資料	否	是
是否可以在多會話中訪問	否	是
是否需要鎖機制	否	是

  結論

綜上所述，大家會發現臨時表和表變數在底層處理機制上是有很多差別的。

簡單地總結，我們對於較小的臨時計算用資料集推薦使用表變數。如果資料集比較大，如果在程式碼中用於臨時計算，同時這種臨時使用永遠都是簡單的全資料集掃描而不需要考慮什麼優化，比如說沒有分組或分組很少的聚合(比如說COUNT、SUM、AVERAGE、MAX等)，也可以考慮使用表變數。使用表變數另外一個考慮因素是應用環境的記憶體壓力，如果程式碼的執行例項很多，就要特別注意記憶體變數對記憶體的消耗。

一般對於大的資料集我們推薦使用臨時表，同時建立索引，或者通過SQL Server的統計資料(Statisitcs)自動建立和維護功能來提供訪問SQL語句的優化。如果需要在多個使用者會話間交換資料，當然臨時表就是唯一的選擇了。需要提及的是，由於臨時表存放在tempdb中，因此要注意tempdb的調優。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9079672/viewspace-166475/，如需轉載，請註明出處，否則將追究法律責任。