TempDB 中表變數和區域性臨時表的對比

參考資料來源：

http://blogs.msdn.com/b/sqlserverstorageengine/archive/tags/tempdb/

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2008/03/30/sql-server-table-variable-vs-local-temporary-table.aspx

我們都知道，tempdb是用來為應用程式和SQL Server臨時儲存執行的中間結果的。由使用者和應用程式建立的物件叫做使用者物件，由SQL
Server引擎產生的物件叫做內部物件，在這篇博文中，我們主要討論使用者物件中的臨時表（#，##）和表變數。大家可能對##表（全域性臨時表）和#表（區域性臨時表）的區別比較瞭解，但對臨時表和表變數卻不是很清楚，下面我們詳述兩者的主要區別。

和其他變數一樣，表變數是一種非常有用的程式構造。表變數的有效範圍和其他程式變數的有效範圍是一樣的。例如，如果你在儲存過程中定義了一個變數，那麼它就不能在儲存過程外被訪問。巧合的是，臨時表也是這樣的。那為什麼我們還要建立表變數呢？因為表變數在儲存過程中可以作為輸出/輸入引數（此功能從SQL
Server2008開始可用）或者用來儲存函式的返回結果。以下是表變數和臨時表的相同和不同之處：

•       首先，表變數不一定常駐記憶體。在記憶體壓力大的時候，屬於表變數的頁可以被放入tempdb。以下是一個例子描述表變數在tempdb中所佔空間。

use tempdb

go

 

drop table #tv_source

go

 

create table #tv_source(c1 int, c2 char(8000))

go

 

declare @i int

select @i = 0

while (@i < 1000)

begin

       insert into #tv_source values (@i, replicate ('a', 100))

       select @i = @i + 1

end

 

DECLARE @tv_target TABLE (c11 int, c22 char(8000))

 

 

 INSERT INTO @tv_target (c11, c22)

    SELECT c1, c2

    FROM  #tv_source

 

-- checking the size through DMV.

-- The sizes here are in 8k pages. This shows the
allocated space

-- to user objects to be 2000 pages (1000 pages
for #tv_source and

-- 1000 pages for @tv_target

 

Select total_size = SUM (unallocated_extent_page_count) +

SUM (user_object_reserved_page_count) +

SUM (internal_object_reserved_page_count) +

SUM (version_store_reserved_page_count) +

SUM (mixed_extent_page_count),

 

SUM (unallocated_extent_page_count) as freespace_pgs,

SUM (user_object_reserved_page_count) as user_obj_pgs,

SUM (internal_object_reserved_page_count) as internal_obj_pgs,

SUM (version_store_reserved_page_count)  as version_store_pgs,

SUM (mixed_extent_page_count) as mixed_extent_pgs

from sys.dm_db_file_space_usage

•         其次，如果您建立了一個表變數，它會像一個常規的DDL操作一樣將後設資料儲存在系統目錄中，以下示例說明了這一點：

declare @ttt TABLE(c111 int, c222 int)

select name from sys.columns where object_id > 100 and name
like 'c%'

結果會返回兩行，包含列C111和C222。這表明如果遇到定義衝突時，把臨時表改成表變數不能解決問題。

•         第三，事務處理和鎖定語句。表變數不能參與事務處理和鎖定，以下示例說明了這一點

-- create a source table

create table
tv_source(c1 int, c2 char(100))

go

 

declare @i int

select @i = 0

while (@i < 100)

begin

   insert into tv_source values (@i, replicate ('a', 100))

   select @i = @i + 1

       end

-- using #table

create table #tv_target (c11 int, c22 char(100))

go

 

BEGIN TRAN

 

    INSERT INTO #tv_target (c11, c22)

           
SELECT c1, c2

           
FROM 
tv_source

 

 

--
using table variable

 

DECLARE @tv_target TABLE (c11 int, c22 char(100))

 

BEGIN TRAN

   INSERT INTO @tv_target (c11, c22)

        SELECT c1, c2

    FROM  tv_source

 

 

-- Now if I look at the locks, you will see that
only

-- #table takes locks. Here is the query that
used

-- to check the locks   

select 

    t1.request_session_id as spid, 

    t1.resource_type as type,  

    t1.resource_database_id as dbid, 

    (case
resource_type

      WHEN 'OBJECT' then object_name(t1.resource_associated_entity_id)

      WHEN 'DATABASE' then ' '

      ELSE (select object_name(object_id) 

           
from sys.partitions 

           
where hobt_id=resource_associated_entity_id)

    END) as objname, 

    t1.resource_description as description,  

    t1.request_mode as mode, 

    t1.request_status as status,

   t2.blocking_session_id

from sys.dm_tran_locks as t1 left outer join sys.dm_os_waiting_tasks as t2

ON t1.lock_owner_address = t2.resource_address

另一個有趣的現象是，如果回滾的事務裡涉及表變數，表變數的資料不會被回滾。

Rollback

-- this
query will return 100 for table variable but 0 for #table.

SELECT COUNT(*) FROM @tv_target

•         第四，表變數上的操作不被日誌檔案記錄。請看下面這個例子：

--
create a table variable, insert bunch of rows and update

DECLARE @tv_target TABLE (c11 int, c22 char(100))

 

INSERT INTO @tv_target (c11, c22)

    SELECT c1, c2

    FROM  tv_source

 

 

-- update all the rows

update @tv_target set c22 = replicate ('b', 100)

 

 

-- look at the top 10 log records. I get no
records for this case

select top 10 operation,context, [log record fixed length], [log record length],
AllocUnitId, AllocUnitName

from fn_dblog(null, null)

where AllocUnitName like '%tv_target%'

order by [Log Record Length] Desc

 

-- create a local temptable

drop table #tv_target

go

 

create table #tv_target (c11 int, c22 char(100))

go

 

 

       INSERT INTO #tv_target (c11, c22)

    SELECT c1, c2

    FROM  tv_source

 

 

--
update all the rows

update #tv_target set c22 = replicate ('b', 100)

 

 

-- look
at the log records. Here I get 100 log records for update

select 
operation,context, [log
record fixed length], [log record length], AllocUnitName

from fn_dblog(null, null)

where AllocUnitName like '%tv_target%'

order by [Log
Record Length] Desc

•         第五，表變數中不允許DDL執行，所以，如果你有一個大的行集需要經常進行查詢，您可能要使用臨時表並建立合適的索引。你可以在宣告表變數時建立唯一約束來解決這個問題。

•         第六，表變數不維護統計資料。這意味著任何表變數資料更改都不會引起相關查詢語句進行重編譯。

•         最後， 涉及表變數的查詢不能生成並行的查詢計劃，因此我們認為對於龐大的臨時資料集最好使用臨時表來發揮並行查詢的優勢。