SQL Server中的暫存資料表和表變數

作者: DrillChina, 　出處:blog,　責任編輯: 李書琴,　2008-07-08 10:05　　在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的調優。

再議SQL Server暫存資料表和表變數

　今天在我和一家軟體公司的開發人員討論資料庫設計調優的時候又討論到了表變數和暫存資料表的問題，覺得這個問題確實是一個爭議比較大的問題。

　　其實從上次發表了表變數和暫存資料表的一個文章http://database.ctocio.com.cn/tips/442/8206442.shtml以來，也有些人留言，也有些人發過郵件討論這個問題。其實表變數和暫存資料表的區別雖然有一些，但是兩者最根本的區別還是在於

　　對儲存的需求：表變數和暫存資料表都消耗Tempdb中的儲存空間，但是進行資料更新的時候，表變數不會寫日誌，而暫存資料表則會寫日誌。(這一點是經過指令碼測試的，表變數並不像我們想象的那樣，唯寫在記憶體而不出現在Tempdb中。)

　　對最佳化的支援：表變數不支援索引和統計資料，暫存資料表則可以支援索引和統計資料。

　　通常需要表變數或者暫存資料表的情況都是一些需要支援臨時計算結果集的地方，那麼就有一些常見的情況了：

　　如果臨時結果集僅僅需要往裡面寫資料，比如通過一個迴圈多次尋找相關資料併合成一個臨時結果集，那麼就可以使用表變數。(結果有人提到了返回結果集的時候需要有排序，但是表變數不支援索引阿。其實這個不要緊，因為表變數雖然不支援索引，但是表變數支援主鍵阿，所以可以利用主鍵來替代索引。)

　　如果臨時結果集不太多需要更改，而是更多地充當一個臨時的關聯資料集去參加各種資料集的串連(JOIN)，那麼索引和統計資料可能會更加適合一些(當然這個臨時結果集要足夠大，這樣索引和統計資料帶來的代價才可以被彌補掉)。

　　由於表變數不支援統計資料，因此在一個預存程序中使用表變數可以減少由於資料變化而導致的重新編譯問題。

　　當然，除了索引和統計資料這個明顯的限制外，表變數同時也不支援並存執行計劃，因此對於大型的臨時結果集，表變數也不是一個好的選擇。

　　前面一個關於表變數和暫存資料表的貼子，有一位robi_xu的朋友提到的問題也確實是在選擇表變數和暫存資料表時候的一些問題。

　　對於函數中不能支援暫存資料表是由於函數不能對函數範圍外部的資源狀態造成永久性的更改，在SQLServer中也稱為副作用(sideeffect)。不過如果在函數中使用大型的臨時結果集是不推薦的，因為如果將這樣的函數放置到一個查詢中會造成很明顯的效能問題，因此這種情況一般都採用預存程序之類的批處理指令碼。

　　對於動態指令碼不支援表變數的原因是因為預存程序不接受表類型的參數。不過如果表變數的聲明和賦值都在sp_executesql的參數中的話，sp_executesql就可以執行了，因為這個時候表變數就存在sp_executesql的stmt參數裡面，不需要傳入，例如下面的代碼：(當然這樣的實用性也就沒有多少了)

　　DECLARE @m nvarchar(max)

　　SET @m = N"DECLARE @t TABLE (ID int);INSERT INTO @tVALUES(1);SELECT * FROM @t T"

　　EXEC sp_executesql @m

　　作者：DrillChina

From:

http://database.ctocio.com.cn/tips/442/8206442.shtml

http://it.hexun.com/2008-07-09/107302733.html