SQL Server 中的臨時表和表變數有什麼區別？
這似乎是一個有很多神話和相互矛盾的觀點的領域。
那麼 SQL Server 中的表變數和本地臨時表有什麼區別呢？
內容
警告
這個答案討論了 SQL Server 2000 中引入的“經典”表變數。記憶體 OLTP 中的 SQL Server 2014 引入了記憶體優化表類型。這些表變數實例在許多方面與下面討論的不同！（更多細節）。
儲存位置
沒有不同。兩者都儲存在tempdb中。
我已經看到它表明對於表變數，情況並非總是如此，但這可以從下面驗證
DECLARE @T TABLE(X INT) INSERT INTO @T VALUES(1),(2) SELECT sys.fn_PhysLocFormatter(%%physloc%%) AS [File:Page:Slot] FROM @T範例結果（在tempdb中顯示儲存 2 行的位置）
File:Page:Slot ---------------- (1:148:0) (1:148:1)邏輯位置
與表相比，表變數的行為更像是目前數據庫的一部分
#temp。對於表變數（自 2005 年以來），如果未明確指定列排序規則，則將是目前數據庫的列排序規則，而對於#temp表，它將使用tempdb的預設排序規則（更多詳細資訊）。使用者定義的數據類型和 XML 集合必須在tempdb中才能用於
#temp表和表變數 ( Source )。表的tempdb中必須存在使用者定義的別名類型，但表變數使用上下文數據庫。#tempSQL Server 2012 引入了包含的數據庫。這些臨時表的行為不同(h/t Aaron)
在包含的數據庫中，臨時表數據在包含的數據庫的排序規則中進行排序。
- 與臨時表關聯的所有元數據（例如，表和列名、索引等）都將在目錄排序規則中。
- 命名約束不能在臨時表中使用。
- 臨時表可能不引用使用者定義的類型、XML 模式集合或使用者定義的函式。
不同範圍的可見性
表變數只能在聲明它們的批處理和範圍內訪問。
#temp可以在子批次（嵌套觸發器、過程、exec呼叫）中訪問表。#temp在外部範圍 (@@NESTLEVEL=0) 創建的表也可以跨越批次，因為它們會一直持續到會話結束。兩種類型的對像都不能在子批處理中創建並在呼叫範圍內訪問，但是如下所述（儘管可以##temp是全域表）。壽命
DECLARE @.. TABLE執行包含語句的批處理時（在該批處理中的任何使用者程式碼執行之前）隱式創建表變數，並在最後隱式刪除。儘管解析器不允許您在
DECLARE語句之前嘗試使用表變數，但可以在下面看到隱式創建。IF (1 = 0) BEGIN DECLARE @T TABLE(X INT) END --Works fine SELECT * FROM @T
#temp表在CREATE TABLE遇到 TSQL 語句時顯式創建，並且可以在批處理結束時顯式刪除DROP TABLE或將在批處理結束時隱式刪除（如果在子批處理中創建@@NESTLEVEL > 0）或會話結束時以其他方式結束。注意：在儲存常式中，兩種類型的對像都可以被記憶體，而不是重複創建和刪除新表。對於何時可以發生這種記憶體有一些限制，但是對於
#temp表可能會違反這些限制，但對錶變數的限制無論如何都會阻止這些限制。#temp記憶體表的維護成本略大於表變數，如此處所示。對像元數據
這對於兩種類型的對象基本上是相同的。它儲存在tempdb的系統基表中。
#temp但是，對於表 來說，查看起來更直接，因為OBJECT_ID('tempdb..#T')它可以用於鍵入系統表，並且內部生成的名稱與CREATE TABLE語句中定義的名稱更密切相關。對於表變數，該object_id函式不起作用，內部名稱完全是系統生成的，與變數名稱無關（名稱是對象 id 的十六進制形式）。下面展示了元數據仍然存在，但是通過鍵入（希望是唯一的）列名。
DBCC PAGE對於沒有唯一列名的表，只要它們不為空，就可以使用 object_id 來確定它們。/*Declare a table variable with some unusual options.*/ DECLARE @T TABLE ( [dba.se] INT IDENTITY PRIMARY KEY NONCLUSTERED, A INT CHECK (A > 0), B INT DEFAULT 1, InRowFiller char(1000) DEFAULT REPLICATE('A',1000), OffRowFiller varchar(8000) DEFAULT REPLICATE('B',8000), LOBFiller varchar(max) DEFAULT REPLICATE(cast('C' as varchar(max)),10000), UNIQUE CLUSTERED (A,B) WITH (FILLFACTOR = 80, IGNORE_DUP_KEY = ON, DATA_COMPRESSION = PAGE, ALLOW_ROW_LOCKS=ON, ALLOW_PAGE_LOCKS=ON) ) INSERT INTO @T (A) VALUES (1),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13) SELECT t.object_id, t.name, p.rows, a.type_desc, a.total_pages, a.used_pages, a.data_pages, p.data_compression_desc FROM tempdb.sys.partitions AS p INNER JOIN tempdb.sys.system_internals_allocation_units AS a ON p.hobt_id = a.container_id INNER JOIN tempdb.sys.tables AS t ON t.object_id = p.object_id INNER JOIN tempdb.sys.columns AS c ON c.object_id = p.object_id WHERE c.name = 'dba.se'輸出
Duplicate key was ignored.在 SQL Server 2012 之前，臨時表和表變數的對象 ID 為正數。從 SQL Server 2012 開始，臨時表和表變數的對象 ID 始終為負數（高位設置）。
交易
對錶變數的操作作為系統事務執行，獨立於任何外部使用者事務，而等效的
#temp表操作將作為使用者事務本身的一部分執行。由於這個原因，一個ROLLBACK命令會影響一個#temp表，但不會改變一個表變數。DECLARE @T TABLE(X INT) CREATE TABLE #T(X INT) BEGIN TRAN INSERT #T OUTPUT INSERTED.X INTO @T VALUES(1),(2),(3) /*Both have 3 rows*/ SELECT * FROM #T SELECT * FROM @T ROLLBACK /*Only table variable now has rows*/ SELECT * FROM #T SELECT * FROM @T DROP TABLE #T日誌記錄
tempdb兩者都為事務日誌生成日誌記錄。一個常見的誤解是表變數並非如此，因此下面的腳本展示了這一點，它聲明了一個表變數，添加了幾行，然後更新它們並刪除它們。因為表變數是在批處理開始和結束時隱式創建和刪除的，所以需要使用多個批處理才能查看完整的日誌記錄。
USE tempdb; /* Don't run this on a busy server. Ideally should be no concurrent activity at all */ CHECKPOINT; GO /* The 2nd column is binary to allow easier correlation with log output shown later*/ DECLARE @T TABLE ([C71ACF0B-47E9-4CAD-9A1E-0C687A8F9CF3] INT, B BINARY(10)) INSERT INTO @T VALUES (1, 0x41414141414141414141), (2, 0x41414141414141414141) UPDATE @T SET B = 0x42424242424242424242 DELETE FROM @T /*Put allocation_unit_id into CONTEXT_INFO to access in next batch*/ DECLARE @allocId BIGINT, @Context_Info VARBINARY(128) SELECT @Context_Info = allocation_unit_id, @allocId = a.allocation_unit_id FROM sys.system_internals_allocation_units a INNER JOIN sys.partitions p ON p.hobt_id = a.container_id INNER JOIN sys.columns c ON c.object_id = p.object_id WHERE ( c.name = 'C71ACF0B-47E9-4CAD-9A1E-0C687A8F9CF3' ) SET CONTEXT_INFO @Context_Info /*Check log for records related to modifications of table variable itself*/ SELECT Operation, Context, AllocUnitName, [RowLog Contents 0], [Log Record Length] FROM fn_dblog(NULL, NULL) WHERE AllocUnitId = @allocId GO /*Check total log usage including updates against system tables*/ DECLARE @allocId BIGINT = CAST(CONTEXT_INFO() AS BINARY(8)); WITH T AS (SELECT Operation, Context, CASE WHEN AllocUnitId = @allocId THEN 'Table Variable' WHEN AllocUnitName LIKE 'sys.%' THEN 'System Base Table' ELSE AllocUnitName END AS AllocUnitName, [Log Record Length] FROM fn_dblog(NULL, NULL) AS D) SELECT Operation = CASE WHEN GROUPING(Operation) = 1 THEN 'Total' ELSE Operation END, Context, AllocUnitName, [Size in Bytes] = COALESCE(SUM([Log Record Length]), 0), Cnt = COUNT(*) FROM T GROUP BY GROUPING SETS( ( Operation, Context, AllocUnitName ), ( ) ) ORDER BY GROUPING(Operation), AllocUnitName退貨
詳細視圖
摘要視圖（包括隱式刪除和系統基表的日誌記錄）
據我所知，兩者上的操作會產生大致相等的日誌記錄。
雖然日誌記錄的數量非常相似，但一個重要的區別是與
#temp表相關的日誌記錄在任何包含使用者事務完成之前不能被清除，因此在某些時候寫入#temp表的長時間執行的事務將阻止日誌截斷，tempdb而自治事務不會為表變數生成。表變數不支持
TRUNCATE，因此當要求從表中刪除所有行時，可能會處於日誌記錄的劣勢（儘管對於非常小的表DELETE無論如何都可以更好地工作）基數
許多涉及表變數的執行計劃將顯示單行估計為它們的輸出。檢查表變數屬性表明 SQL Server 認為表變數有零行（Paul White 解釋了為什麼它估計會從零行表中發出一行）。
但是，上一節中顯示的結果確實顯示了準確的
rows計數sys.partitions。問題是在大多數情況下，引用表變數的語句是在表為空時編譯的。如果在填充表變數後（重新）編譯語句，則目前行數將用於表基數（這可能是由於顯式recompile或可能因為語句還引用了另一個導致延遲編譯的對像或重新編譯。）DECLARE @T TABLE(I INT); INSERT INTO @T VALUES(1),(2),(3),(4),(5) CREATE TABLE #T(I INT) /*Reference to #T means this statement is subject to deferred compile*/ SELECT * FROM @T WHERE NOT EXISTS(SELECT * FROM #T) DROP TABLE #T計劃顯示延遲編譯後的準確估計行數。
在 SQL Server 2012 SP2 中，引入了跟踪標誌 2453。更多詳細資訊在此處的“關係引擎”下。
當啟用此跟踪標誌時，它可能會導致自動重新編譯以考慮更改的基數，正如稍後將進一步討論的那樣。
注意：在兼容級別 150 的 Azure 上，該語句的編譯現在推遲到第一次執行。這意味著它將不再受到零行估計問題的影響。
無列統計
然而，擁有更準確的表基數並不意味著估計的行數會更準確（除非對錶中的所有行進行操作）。SQL Server 根本不維護表變數的列統計資訊，因此將依賴於基於比較謂詞的猜測（例如，
=針對非唯一列將返回 10% 的表，或針對>比較返回 30% 的表）。相反，為表維護列統計資訊#temp。SQL Server 維護對每列所做的修改次數的計數。如果自編譯計劃以來的修改次數超過重新編譯門檻值 (RT)，則將重新編譯計劃並更新統計資訊。RT 取決於表類型和大小。
RT 計算如下。（n 指編譯查詢計劃時表的基數。）
常設桌
- 如果 n <= 500，則 RT = 500。
- 如果 n > 500，則 RT = 500 + 0.20 * n。
臨時表
- 如果 n < 6，則 RT = 6。
- 如果 6 <= n <= 500，則 RT = 500。
- 如果 n > 500，則 RT = 500 + 0.20 * n。
表變數
- RT 不存在。因此，不會因為表變數的基數變化而發生重新編譯。 （但請參閱下面關於 TF 2453 的說明）
該
KEEP PLAN提示可用於將表的 RT 設置為#temp與永久表相同。所有這一切的最終結果是，
#temp當涉及許多行時，為表生成的執行計劃通常比為表變數生成的執行計劃好幾個數量級，因為 SQL Server 有更好的資訊可以使用。NB1：表變數沒有統計資訊，但仍會在跟踪標誌 2453 下引發“統計資訊已更改”重新編譯事件（不適用於“瑣碎”計劃）這似乎發生在與上述臨時表相同的重新編譯門檻值下額外的一個 if
N=0 -> RT = 1。即當表變數為空時編譯的所有語句最終將在非空時第一次執行時重新編譯並更正*TableCardinality 。*編譯時間表基數儲存在計劃中，如果語句以相同的基數再次執行（由於控制語句的流或記憶體計劃的重用），則不會發生重新編譯。NB2：對於儲存過程中的記憶體臨時表，重新編譯的故事比上面描述的要復雜得多。有關所有詳細資訊，請參閱儲存過程中的臨時表。
重新編譯
除了上面描述的基於修改的重新編譯之外，
#temp表還可以與其他編譯相關聯，因為它們允許對觸發編譯的表變數進行禁止的操作（例如 DDL 更改CREATE INDEX，ALTER TABLE）鎖定
已經聲明表變數不參與鎖定。不是這種情況。將以下輸出執行到 SSMS 消息選項卡，以獲取為插入語句獲取和釋放的鎖的詳細資訊。
DECLARE @tv_target TABLE (c11 int, c22 char(100)) DBCC TRACEON(1200,-1,3604) INSERT INTO @tv_target (c11, c22) VALUES (1, REPLICATE('A',100)), (2, REPLICATE('A',100)) DBCC TRACEOFF(1200,-1,3604)對於
SELECT來自表變數的查詢，Paul White 在註釋中指出這些自動帶有隱式NOLOCK提示。如下所示DECLARE @T TABLE(X INT); SELECT X FROM @T OPTION (RECOMPILE, QUERYTRACEON 3604, QUERYTRACEON 8607)輸出
*** Output Tree: (trivial plan) *** PhyOp_TableScan TBL: @T Bmk ( Bmk1000) IsRow: COL: IsBaseRow1002 Hints( NOLOCK )然而，這對鎖定的影響可能很小。
SET NOCOUNT ON; CREATE TABLE #T( [ID] [int] IDENTITY NOT NULL, [Filler] [char](8000) NULL, PRIMARY KEY CLUSTERED ([ID] DESC)) DECLARE @T TABLE ( [ID] [int] IDENTITY NOT NULL, [Filler] [char](8000) NULL, PRIMARY KEY CLUSTERED ([ID] DESC)) DECLARE @I INT = 0 WHILE (@I < 10000) BEGIN INSERT INTO #T DEFAULT VALUES INSERT INTO @T DEFAULT VALUES SET @I += 1 END /*Run once so compilation output doesn't appear in lock output*/ EXEC('SELECT *, sys.fn_PhysLocFormatter(%%physloc%%) FROM #T') DBCC TRACEON(1200,3604,-1) SELECT *, sys.fn_PhysLocFormatter(%%physloc%%) FROM @T PRINT '--*--' EXEC('SELECT *, sys.fn_PhysLocFormatter(%%physloc%%) FROM #T') DBCC TRACEOFF(1200,3604,-1) DROP TABLE #T這些返回結果都不是索引鍵順序，表明 SQL Server 對兩者都使用了分配順序掃描。
我執行了上述腳本兩次，第二次執行的結果如下
Process 58 acquiring Sch-S lock on OBJECT: 2:-1325894110:0 (class bit0 ref1) result: OK --*-- Process 58 acquiring IS lock on OBJECT: 2:-1293893996:0 (class bit0 ref1) result: OK Process 58 acquiring S lock on OBJECT: 2:-1293893996:0 (class bit0 ref1) result: OK Process 58 releasing lock on OBJECT: 2:-1293893996:0表變數的鎖定輸出確實非常少，因為 SQL Server 只是在對像上獲取了架構穩定性鎖。但是對於一個
#temp表來說，它幾乎和它一樣輕，因為它取出了一個對象級S鎖。當然，在使用表時也可以顯式指定NOLOCK提示或隔離級別。READ UNCOMMITTED``#temp與記錄周圍使用者事務的問題類似，這可能意味著表的鎖定時間更長
#temp。使用下面的腳本--BEGIN TRAN; CREATE TABLE #T (X INT,Y CHAR(4000) NULL); INSERT INTO #T (X) VALUES(1) SELECT CASE resource_type WHEN 'OBJECT' THEN OBJECT_NAME(resource_associated_entity_id, 2) WHEN 'ALLOCATION_UNIT' THEN (SELECT OBJECT_NAME(object_id, 2) FROM tempdb.sys.allocation_units a JOIN tempdb.sys.partitions p ON a.container_id = p.hobt_id WHERE a.allocation_unit_id = resource_associated_entity_id) WHEN 'DATABASE' THEN DB_NAME(resource_database_id) ELSE (SELECT OBJECT_NAME(object_id, 2) FROM tempdb.sys.partitions WHERE partition_id = resource_associated_entity_id) END AS object_name, * FROM sys.dm_tran_locks WHERE request_session_id = @@SPID DROP TABLE #T -- ROLLBACK對於這兩種情況，當在顯式使用者事務之外執行時，檢查時返回的唯一鎖
sys.dm_tran_locks是DATABASE.取消註釋
BEGIN TRAN ... ROLLBACK時返回 26 行，表明對象本身和系統表行都持有鎖，以允許回滾並防止其他事務讀取未送出的數據。等效的表變數操作不受使用者事務回滾的影響，並且不需要為我們在下一條語句中檢查而持有這些鎖，但是跟踪在 Profiler 中獲取和釋放的鎖或使用跟踪標誌 1200 顯示大量鎖定事件仍然存在發生。索引
對於 SQL Server 2014 之前的版本，索引只能在表變數上隱式創建，這是添加唯一約束或主鍵的副作用。這當然意味著只支持唯一索引。
UNIQUE NONCLUSTERED可以通過簡單地聲明它並將 CI 鍵添加到所需 NCI 鍵的末尾來模擬具有唯一聚集索引的表上的非唯一非聚集索引（SQL Server無論如何都會在幕後執行此操作，即使非唯一NCI 可以指定）如前所述
index_option，可以在約束聲明中指定各種 s ，包括DATA_COMPRESSION,IGNORE_DUP_KEY, andFILLFACTOR（儘管設置它沒有意義，因為它只會對索引重建產生任何影響，並且您不能重建表變數上的索引！）此外，表變數不支持
INCLUDEd 列、過濾索引（直到 2016 年）或分區，#temp表支持（分區方案必須在 中創建tempdb）。SQL Server 2014 中的索引
可以在 SQL Server 2014 的表變數定義中內聯聲明非唯一索引。範例語法如下。
DECLARE @T TABLE ( C1 INT INDEX IX1 CLUSTERED, /*Single column indexes can be declared next to the column*/ C2 INT INDEX IX2 NONCLUSTERED, INDEX IX3 NONCLUSTERED(C1,C2) /*Example composite index*/ );SQL Server 2016 中的索引
從 CTP 3.1 開始，現在可以為表變數聲明過濾索引。通過 RTM，可能會允許包含的列，儘管由於資源限制它們可能不會進入 SQL16
DECLARE @T TABLE ( c1 INT NULL INDEX ix UNIQUE WHERE c1 IS NOT NULL /*Unique ignoring nulls*/ )並行性
插入（或以其他方式修改）表變數的查詢不能有並行計劃，
#temp表不受這種方式的限制。有一個明顯的解決方法，如下重寫確實允許
SELECT部分並行發生，但最終使用隱藏的臨時表（在幕後）INSERT INTO @DATA ( ... ) EXEC('SELECT .. FROM ...')從表變數中選擇的查詢沒有這樣的限制，如我在此處的回答中所示
其他功能差異
#temp_tables不能在函式內部使用。表變數可以在標量或多語句表 UDF 中使用。- 表變數不能有命名約束。
- 表變數不能是
SELECT-edINTO、ALTER-ed 、TRUNCATEd 或作為or of等DBCC命令的目標，並且不支持表提示，例如DBCC CHECKIDENT``SET IDENTITY INSERT``WITH (FORCESCAN)CHECK為了簡化、隱含謂詞或矛盾檢測，優化器不考慮對錶變數的約束。- 表變數不符合行集共享優化的條件，這意味著針對這些變數的刪除和更新計劃可能會遇到更多成本和
PAGELATCH_EX等待。（例）僅記憶？
如開頭所述，兩者都儲存在tempdb的頁面上。但是，在將這些頁面寫入持久儲存時，我沒有說明在行為上是否存在任何差異。
我現在已經對此進行了少量測試，到目前為止還沒有看到這種差異。在我對 SQL Server 250 頁面實例進行的特定測試中，似乎是數據文件被寫入之前的截止點。
注意：在 SQL Server 2014 或SQL Server 2012 SP1/CU10 或 SP2/CU1中不再出現以下行為，急切的編寫器不再急切地刷新頁面。有關SQL Server 2014更改的更多詳細資訊： tempdb 隱藏的性能寶石。
執行以下腳本
CREATE TABLE #T(X INT, Filler char(8000) NULL) INSERT INTO #T(X) SELECT TOP 250 ROW_NUMBER() OVER (ORDER BY @@SPID) FROM master..spt_values DROP TABLE #T使用 Process Monitor 監控對tempdb數據文件的寫入，我沒有看到任何內容（除了偶爾在偏移 73,728 處的數據庫引導頁面）。將 250 更改為 251 後，我開始看到如下寫入。
上面的螢幕截圖顯示了 5 * 32 頁寫入和一個單頁寫入，表示已寫入 161 頁。在使用表變數進行測試時，我也得到了 250 頁的相同截止點。下面的腳本通過查看以不同的方式顯示它
sys.dm_os_buffer_descriptorsDECLARE @T TABLE ( X INT, [dba.se] CHAR(8000) NULL) INSERT INTO @T (X) SELECT TOP 251 Row_number() OVER (ORDER BY (SELECT 0)) FROM master..spt_values SELECT is_modified, Count(*) AS page_count FROM sys.dm_os_buffer_descriptors WHERE database_id = 2 AND allocation_unit_id = (SELECT a.allocation_unit_id FROM tempdb.sys.partitions AS p INNER JOIN tempdb.sys.system_internals_allocation_units AS a ON p.hobt_id = a.container_id INNER JOIN tempdb.sys.columns AS c ON c.object_id = p.object_id WHERE c.name = 'dba.se') GROUP BY is_modified結果
顯示已寫入 192 頁並清除了臟標誌。它還表明，被寫入並不意味著頁面將立即從緩衝池中逐出。仍然可以完全從記憶體中滿足針對此表變數的查詢。
在
max server memory設置為 2000 MB 並DBCC MEMORYSTATUS報告 Buffer Pool Pages Allocated 為大約 1,843,000 KB（c. 23,000 頁）的空閒伺服器上，我以 1,000 行/頁的批次插入到上面的表中，並記錄了每次迭代。SELECT Count(*) FROM sys.dm_os_buffer_descriptors WHERE database_id = 2 AND allocation_unit_id = @allocId AND page_type = 'DATA_PAGE'表變數和
#temp表都給出了幾乎相同的圖表，並且在達到它們沒有完全保存在記憶體中之前設法幾乎最大化緩衝池，因此似乎對多少記憶體沒有任何特別的限制兩者都可以消費。
我想根據特定經驗而不是研究來指出一些事情。作為一名 DBA，我很新，所以請在需要的地方糾正我。
- #temp 表預設使用 SQL Server 實例的預設排序規則。因此，除非另有說明，否則如果 masterdb 與數據庫的排序規則不同，您可能會在比較或更新 #temp 表和數據庫表之間的值時遇到問題。請參閱：http ://www.mssqltips.com/sqlservertip/2440/create-sql-server-temporary-tables-with-the-correct-collation/
- 完全基於個人經驗，可用記憶體似乎對哪個性能更好有影響。MSDN 建議使用表變數來儲存較小的結果集，但大多數情況下差異甚至不明顯。然而，在較大的集合中，很明顯在某些情況下，表變數會佔用更多的記憶體，並且可以將查詢減慢到爬行速度。