在思考表變量與臨時表之間區別時，表變量不會受事務回滾的影響，那么是否意味著表變量無需寫入日志呢？

測試方式：

分別對tempdb上的用戶表/臨時表/表變量 進行10000次插入，查看日志寫入次數，使用SYS.dm_io_virtual_file_stats可以查看數據庫文件的讀入和寫入次數，也可以使用PRocess monitor來捕獲在tempdb的日志文件上的操作

1. 測試用戶表

--=================================================--測試tempdb上的表USE tempdbGOCREATE TABLE TB1(    ID INT)GOSELECT * FROM SYS.dm_io_virtual_file_stats(2,1)SELECT * FROM SYS.dm_io_virtual_file_stats(2,2)DECLARE @ID INTSET @ID=0WHILE(@ID<10000)BEGININSERT INTO TB1SELECT @IDSET @ID=@ID+1ENDSELECT * FROM SYS.dm_io_virtual_file_stats(2,1)SELECT * FROM SYS.dm_io_virtual_file_stats(2,2)

2. 測試表變量

SELECT * FROM SYS.dm_io_virtual_file_stats(2,1)SELECT * FROM SYS.dm_io_virtual_file_stats(2,2)DECLARE  @TB1 TABLE(ID INT)DECLARE @ID INTSET @ID=0WHILE(@ID<10000)BEGININSERT INTO @TB1SELECT @IDSET @ID=@ID+1ENDSELECT * FROM SYS.dm_io_virtual_file_stats(2,1)SELECT * FROM SYS.dm_io_virtual_file_stats(2,2)

3.測試臨時表

SELECT * FROM SYS.dm_io_virtual_file_stats(2,1)SELECT * FROM SYS.dm_io_virtual_file_stats(2,2)CREATE TABLE #TB1 (ID INT)DECLARE @ID INTSET @ID=0WHILE(@ID<10000)BEGININSERT INTO #TB1SELECT @IDSET @ID=@ID+1ENDSELECT * FROM SYS.dm_io_virtual_file_stats(2,1)SELECT * FROM SYS.dm_io_virtual_file_stats(2,2)

部分截圖：

測試結果：

對邊兩次SYS.dm_io_virtual_file_stats的結果，可以發現日志上發生41次寫操作，而process monitor也驗證了41次寫操作，每次寫操作寫入61kb數據(再次證明每次日志最大寫入量)

無論是tempdb上的用戶表還是表標量以及臨時表，相同的操作所引發的日志寫入次數是一樣的，寫入的總日志大小也大致相同。

--================================================================

在MS網站上找到如下：

涉及表變量的事務僅維持表變量上更新的持續時間。因此，使用表變量時，需要鎖定和記錄資源的情況更少。因為表變量具有有限的范圍并且不是持久性數據庫的一部分，所以事務回滾并不影響它們。

原文地址：http://support.microsoft.com/kb/305977/zh-cn

強烈推薦各位讀一讀這一篇

--=================================================================

查看操作臨時表的日志：

（注：表變量在tempdb中以臨時表的方式出現）

--==================================================================

個人猜想1：

既然MS把表變量的操作也記錄在日志中，那么肯定有其保存的原因，在上面的測試過程中，表變量單獨運行，但在很多事務中，表變量會參與其他用戶對象的修改操作（如關聯更新），因此需要將其變化記錄到日志中，以便后續使用。

個人猜想2：

MS在SQL SERVER底層的一些操作上，把表變量和臨時表當成同一類型來處理，因此調用了和臨時表相同的方法來處理表變量，因此導致表變量也寫入日志。

--==================================================================

關于tempdb上的redo和undo

由于tempdb在每次實例重啟后重新創建，不需要對tempdb上的數據做持久化處理，也不會發生redo操作，因此在記錄日志時，不需要記錄after image,只記錄before image用于事務回滾操作。

PS:在tempdb上不會每次commit都觸發寫日志操作（本文上述測試中執行10000次事務，但只發生了41次寫）。

--==================================================================

相關鏈接：

Understanding data vs log usage for spills in tempdb

Working with tempdb in SQL Server 2005

存儲過程重新編譯

--===================================================================

照例是妹子鎮貼：