再議臨時表和表變數

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

其實從上次發表了表變數和臨時表的一個帖子以來，也有些人留言，也有些人發過郵件討論這個問題。其實表變數和臨時表的區別雖然有一些，但是兩者最根本的區別還是在於

1. 對儲存的需求：表變數和臨時表都消耗Tempdb中的儲存空間，但是進行資料更新的時候，表變數不會寫日誌，而臨時表則會寫日誌。(這一點是經過指令碼測試的，表變數並不像我們想象的那樣，只寫在記憶體而不出現在Tempdb中。)
2. 對優化的支援：表變數不支援索引和統計資料，臨時表則可以支援索引和統計資料。

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

• 如果臨時結果集僅僅需要往裡面寫資料，比如通過一個迴圈多次查詢相關資料併合成一個臨時結果集，那麼就可以使用表變數。(結果有人提到了返回結果集的時候需要有排序，但是表變數不支援索引阿。其實這個不要緊，因為表變數雖然不支援索引，但是表變數支援主鍵阿，所以可以利用主鍵來替代索引。)
• 如果臨時結果集不太多需要更改，而是更多地充當一個臨時的關聯資料集去參加各種資料集的連線(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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9079672/viewspace-368839/，如需轉載，請註明出處，否則將追究法律責任。