SQL Server暫存資料表和表變數(2)

　　其實表變數和暫存資料表的區別雖然有一些，但是兩者最根本的區別還是在於

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

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

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

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

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

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

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

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

　　對於函數中不能支援暫存資料表是由於函數不能對函數範圍外部的資源狀態造成永久性的更改，在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