【轉載】SQL Server中的暫存資料表和表變數的區別

1，

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

在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的調優。

 

2，

轉載：http://blog.csdn.net/lovehongyun/archive/2008/01/09/2031260.aspx

 

問題 1：為什麼在已經有了暫存資料表的情況下還要引入表變數？

解答 1：與暫存資料表相比，表變數具有下列優點： • 如 SQL Server 聯機叢書“表”(Table) 一文中所述，表變數（如局部變數）具有明確定義的範圍，在該範圍結束時會自動清除這些表變數。
• 與暫存資料表相比，表變數導致預存程序的重新編譯更少。
• 涉及表變數的事務僅維持表變數上更新的期間。因此，使用表變數時，需要鎖定和記錄資源的情況更少。因為表變數具有有限的範圍並且不是持久性資料庫的一部分，所以交易回復並不影響它們。
問題 2：如果說使用表變數比使用暫存資料表導致預存程序的重新編譯更少，這意味著什嗎？

解答 2：下面的文章討論了重新編譯預存程序的一些原因：

243586 (http://support.microsoft.com/kb/243586/) 預存程序重新編譯的疑難解答
“由於某些暫存資料表操作引起的重新編譯”一節還列出了為避免一些問題（例如使用暫存資料表導致重新編譯）而需要滿足的一些要求。這些限制不適用於表變數。

表變數完全獨立於建立這些表變數的批，因此，當執行 CREATE 或 ALTER 語句時，不會發生“重新解析”，而在使用暫存資料表時可能會發生“重新解析”。暫存資料表需要此“重新解析”，以便從嵌套預存程序引用該表。表變數完全避免了此問題，因此預存程序可以使用已編譯的計劃，從而節省了處理預存程序的資源。

問題 3：表變數有哪些缺陷？

解答 3：與暫存資料表相比，它存在下列缺陷： • 在表變數上不能建立非叢集索引（為 PRIMARY 或 UNIQUE 約束建立的系統索引除外）。與具有非叢集索引的暫存資料表相比，這可能會影響查詢效能。
• 表變數不像暫存資料表那樣可以維護統計資訊。在表變數上，不能通過自動建立或使用 CREATE STATISTICS 語句來建立統計資訊。因此，在大表上進行複雜查詢時，缺少統計資訊可能會妨礙最佳化器確定查詢的最佳計劃，從而影響該查詢的效能。
• 在初始 DECLARE 語句後不能更改表定義。
• 表變數不能在 INSERT EXEC 或 SELECT INTO 語句中使用。
• 表型別宣告中的檢查約束、預設值以及計算所得的列不能調用使用者定義的函數。
• 如果表變數是在 EXEC 語句或 sp_executesql 預存程序外建立的，則不能使用 EXEC 語句或 sp_executesql 預存程序來運行引用該表變數的動態 SQL Server 查詢。由於表變數只能在它們的本地範圍中引用，因此 EXEC 語句和 sp_executesql 預存程序將在表變數的範圍之外。但是，您可以在 EXEC 語句或 sp_executesql 預存程序內建立表變數並執行所有處理，因為這樣表變數本地範圍將位於 EXEC 語句或 sp_executesql 預存程序中。
問題 4：與暫存資料表或永久表相比，表變數的僅存在於記憶體中的結構保證了更好的效能，是否因為它們是在駐留在物理磁碟上的資料庫中維護的？

解答 4：表變數不是僅存在於記憶體中的結構。由於表變數可能保留的資料較多，記憶體中容納不下，因此它必須在磁碟上有一個位置來儲存資料。與暫存資料表類似，表變數是在 tempdb 資料庫中建立的。如果有足夠的記憶體，則表變數和暫存資料表都在記憶體（資料緩衝）中建立和處理。

問題 5：必須使用表變數來代替暫存資料表嗎？

解答 5：答案取決於以下三個因素： • 插入到表中的行數。
• 從中儲存查詢的重新編譯的次數。
• 查詢類型及其對效能的指數和統計資訊的依賴性。
在某些情況下，可將一個具有暫存資料表的預存程序拆分為多個較小的預存程序，以便在較小的單元上進行重新編譯。

通常情況下，應盡量使用表變數，除非資料量非常大並且需要重複使用表。在這種情況下，可以在暫存資料表上建立索引以提高查詢效能。但是，各種方案可能互不相同。Microsoft 建議您做一個測試，來驗證表變數對於特定的查詢或預存程序是否比暫存資料表更有效。