1. 測試創建表變量對IO的影響

測試創建表變量前后，tempdb的空間大小，目前使用sp_spaceused得到大小，也可以使用視圖sys.dm_db_file_space_usage

use tempdbgoSet nocount onExec sp_spaceused /*插入數據之前*/declare @tmp_orders table ( list_no int,id int)insert into @tmp_orders(list_no,id)select ROW_NUMBER() over( order by Id  ) list_no,idfrom Test.dbo.Orders             Select top(1) name,object_id,type,create_datefrom sys.objects Where type='U' Order by create_date Desc            Exec sp_spaceused /*插入數據之后*/GoExec sp_spaceused /*Go之后*/

執行結果如下：

可以看到：

1）在表變量創建完畢，同時批處理語句沒有結束時，臨時庫的空間增大了接近9M空間。創建表變量的語句結束后，空間釋放

2）在臨時庫的對象表sys.objects中能夠查詢到剛剛創建的表變量對象

繼續驗證是否發生IO操作，使用視圖sys.dm_io_virtual_file_stats

在創建表變量前后執行如下語句：

select db_name(database_id) database_name,*from sys.dm_io_virtual_file_stats(db_id('tempdb'), NULL)

測試結果如下：

1* 創建表變量前

2*創建表變量后

declare @tmp_orders table ( list_no int,id int)insert into @tmp_orders(list_no,id)select ROW_NUMBER() over( order by Id  ) list_no,idfrom Test.dbo.Orders   --查詢tempdb庫中最后創建的對象         Select top(1) name,object_id,type,create_date from sys.objects Where type='U' Order by create_date Desc  --查詢內存中緩存頁數 SELECT count(*)AS cached_pages_count     ,name ,index_id FROM sys.dm_os_buffer_descriptors AS bd     INNER JOIN     (        SELECT object_name(object_id) AS name             ,index_id ,allocation_unit_id        FROM sys.allocation_units AS au            INNER JOIN sys.partitions AS p                 ON au.container_id = p.hobt_id                     AND (au.type = 1 OR au.type = 3)        UNION ALL        SELECT object_name(object_id) AS name               ,index_id, allocation_unit_id        FROM sys.allocation_units AS au            INNER JOIN sys.partitions AS p                 ON au.container_id = p.partition_id                     AND au.type = 2    ) AS obj         ON bd.allocation_unit_id = obj.allocation_unit_idWHERE database_id = db_id()GROUP BY name, index_id ORDER BY cached_pages_count DESC

測試結果如下：

可以看到表變量創建后，數據頁面也會緩存在Buffer Pool中。但所在的批處理語句結束后，占用空間會被釋放。

3. 結論

SQL Server在批處理中創建的表變量會產生IO操作，占用tempdb的空間，以及內存bufferPool的空間。在所在批處理結束后，占用會被清除

參考文章：

http://www.cnblogs.com/CareySon/archive/2012/06/11/2544835.html

http://www.cnblogs.com/wghao/archive/2011/11/02/2227219.html