參考資料來源：
http://blogs.msdn.com/b/sqlserverstorageengine/archive/tags/tempdb/
我們都知道，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運行，所以，如果你有一個大的行集需要經常進行查詢，您可能要使用臨時表并創建合適的索引。你可以在聲明表變量時創建唯一約束來解決這個問題。
????????? 第六，表變量不維護統計數據。這意味著任何表變量數據更改都不會引起相關查詢語句進行重編譯。
????????? 最后， 涉及表變量的查詢不能生成并行的查詢計劃，因此我們認為對于龐大的臨時數據集最好使用臨時表來發揮并行查詢的優勢。
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
