SQL Server 表變數與暫存資料表區別

問題 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 建議您做一個測試，來驗證表變數對於特定的查詢或預存程序是否比暫存資料表更有效。