sql:臨時表和表變量

在SQL Server的性能調優中，有一個不可比拟的問題：那就是如何在一段需要長時間的代碼或被頻繁調用的代碼中處理臨時資料集，表變量和臨時表是兩種選擇。

臨時表：

臨時對象都以#或##為字首，臨時表是臨時對象的一種，還有例如臨時存儲過程、臨時函數之類的臨時對象，臨時對象都存儲在tempdb中。

以#字首的臨時表為本地的，是以隻有在目前使用者會話中才可以通路，而##字首的臨時表是全局的，是以所有使用者會話都可以通路。

臨時表以會話為邊界，隻要建立臨時表的會話沒有結束，臨時表就會持續存在，當然使用者在會話中可以通過DROP TABLE指令提前銷毀臨時表。

臨時表存儲在tempdb中，是以臨時表的通路是有可能造成實體IO的，當然在修改時也需要生成日志來確定一緻性，同時鎖機制也是不可缺少的。

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

CREATE TABLE dbo.#News 
　　( 
　　News_id int NOT NULL, 
　　NewsTitle varchar(100), 
　　NewsContent varchar(2000), 
　　NewsDateTime datetime 
　　) 
　　INSERT INTO dbo.#News (News_id, NewsTitle, NewsContent, NewsDateTime) 
　　VALUES (1,'BlueGreen', 'Austen', 200801, GETDATE()) 
　　SELECT News_id, NewsTitle, NewsContent, NewsDateTime FROM dbo.#News 
　　DROP TABLE dbo.[#News]      

表變量：

變量都以@或@@為字首，表變量是變量的一種，另外一種變量被稱為标量(可以了解為标準變量，就是标準資料類型的變量，例如整型int或者日期型DateTime)。

以@字首的表變量是本地的，是以隻有在目前使用者會話中才可以通路，而@@字首的表變量是全局的，通常都是系統變量，比如說@@error代表最近的一個T-SQL語句的報錯号。

當然因為表變量首先是個變量，是以它隻能在一個Batch中生存，也就是我們所說的邊界，超出了這個邊界，表變量也就消亡了。

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

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

DECLARE @News table 
　　( 
　　News_id int NOT NULL, 
　　NewsTitle varchar(100), 
　　NewsContent varchar(2000), 
　　NewsDateTime datetime 
　　) 
　　INSERT INTO @News (News_id, NewsTitle, NewsContent, NewsDateTime) 
　　VALUES (1,'BlueGreen', 'Austen', 200801, GETDATE()) 
　　SELECT News_id, NewsTitle, NewsContent, NewsDateTime FROM @News      

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

結論：

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

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

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

問題：

對于函數中不能支援臨時表是由于函數不能對函數作用域外部的資源狀态造成永久性的更改，在SQL Server中也稱為副作用(side effect)。

不過如果在函數中使用大型的臨時結果集是不推薦的，因為如果将這樣的函數放置到一個查詢中會造成很明顯的性能問題，是以這種情況一般都采用存儲過程之類的批處理腳本。

對于動态腳本不支援表變量的原因是因為存儲過程不接受表類型的參數。

不過如果表變量的聲明和指派都在sp_executesql的參數中的話，sp_executesql就可以執行了，因為這個時候表變量就存在sp_executesql的stmt參數裡面，不需要傳入，

例如下面的代碼：(當然這樣的實用性也就沒有多少了)

DECLARE @m nvarchar(max)

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

　　EXEC sp_executesql @m