那么表变量和SQL Server中的本地临时表之间有什么区别？
#1 楼
目录
Caveat
此答案讨论了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)
逻辑位置
@table_variables的行为比#temp表更像它们是当前数据库的一部分。对于表变量（自2005年起），如果未明确指定，则列排序规则将是当前数据库的列排序规则，而对于#temp表，它将使用默认排序规则tempdb（更多详细信息）。此外，用户定义的数据类型和XML集合必须在tempdb中才能用于#temp表，但表变量可以从当前数据库（源）中使用它们。SQL Server 2012引入了包含的数据库。临时表在这些表中的行为有所不同（h / t Aaron）
在包含的数据库中，临时表的数据按包含的数据库的排序规则进行排序。
所有关联的元数据目录排序规则中将包含临时表（例如，表和列名，索引等）。
临时表中可能不会使用命名约束。
临时表可能未引用用户定义的类型，XML模式集合或用户定义的函数。
对不同范围的可见性
@table_variables只能在声明它们的批次和范围内访问。在子批处理中可访问#temp_tables（嵌套触发器，过程，exec调用）。在外部范围（#temp_tables）上创建的@@NESTLEVEL=0可以跨越批次，因为它们会持续到会话结束。不能在子批处理中创建两种类型的对象，也不能在调用范围内访问这两种类型的对象，如下所述（可以是全局##temp表）。已执行（在该批处理中的任何用户代码运行之前），并在末尾隐式删除。尽管解析器不允许您在
@table_variables语句之前尝试使用表变量，但可以在下面看到隐式创建。 IF (1 = 0)
BEGIN
DECLARE @T TABLE(X INT)
END
--Works fine
SELECT *
FROM @T
DECLARE @.. TABLE是在遇到TSQL DECLARE语句时显式创建的，可以用#temp_tables显式删除，或者在批处理结束时（如果使用CREATE TABLE在子批中创建）或会话时隐式删除。 NB：在存储的例程中，可以缓存两种类型的对象，而不必重复创建和删除新表。关于何时可以进行此缓存存在一些限制，但是对于
DROP TABLE可能会违反这些限制，但是无论如何，这些限制会阻止。缓存的@@NESTLEVEL > 0表的维护开销略大于表变量的维护开销，如此处所示。对象元数据
对于两种类型的对象，这基本上是相同的。它存储在
#temp_tables的系统基本表中。对于@table_variables表，可以更直接地看到，但是因为可以使用#temp键入系统表，并且内部生成的名称与tempdb语句中定义的名称更紧密相关。对于表变量，#temp函数不起作用，内部名称完全由系统生成，与变量名称无关。下面通过键入（希望唯一）列名演示了元数据仍然存在。对于没有唯一列名的表，只要它们不为空，就可以使用OBJECT_ID('tempdb..#T')来确定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.
object_id
名称
行
type_desc
总页数
used_pages
数据页
data_compression_desc
＃22401542
13
IN_ROW_DATA
2
2
1
< br 574574090
＃22401542
13
LOB_DATA
24
19
0
PAGE
574625090
＃22401542
13
ROW_OVERFLOW_DATA
16
14
0
PAGE
574625090
＃22401542
13
IN_ROW_DATA
2
2
1
无
事务
CREATE TABLE上的操作作为系统事务执行，独立于任何外部用户事务，而等效的object_id表操作将作为用户事务本身的一部分执行。因此，DBCC PAGE命令将影响@table_variables表，但不影响#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
记录
两者都生成日志记录到
ROLLBACK事务日志。一个常见的误解是表变量不是这种情况，因此下面的脚本演示了这一点，它声明了一个表变量，添加了几行然后更新了它们并删除了它们。因为创建了表变量并在批处理的开始和结束处隐式删除数据，必须使用多个批处理才能查看完整的日志记录。
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
返回
###详细视图
###摘要视图（包括隐式drop和系统基表的日志记录）
据我所知，两者的操作均生成大致相等的日志记录。 br />尽管日志记录的数量非常相似，但一个重要的区别是与
#temp表相关的日志记录要等到任何包含用户事务的事务完成后才能清除，因此长时间运行的事务有时会写入@table_variable表，这将防止日志被截断在tempdb中，而自主交易为表变量而生成的ns不支持。表变量不支持
#temp，因此在要求从表中删除所有行时可能在记录方面处于不利地位（尽管对于很小的表#temp仍然可以更好地工作）<基数许多涉及表变量的执行计划将显示估计为它们输出的一行。检查表变量的属性表明，SQL Server认为表变量具有零行（为什么它估计将从零行表发出1行，这在@Paul White的说明中进行了说明。）
但是，上一节中显示的结果的确在
tempdb中显示了准确的TRUNCATE计数。问题是，在大多数情况下，引用表变量的语句在表为空时进行编译。如果在填充DELETE之后对语句进行了（重新）编译，则将其用于表基数（这可能是由于显式rows所致，或者可能是因为该语句还引用了另一个导致延迟编译或重新编译的对象。） 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。更多详细信息在此处的“关系引擎”下。
启用此跟踪标志时，它可能导致自动重新编译考虑更改的基数，这将在不久后进一步讨论。
NB：在Azure上的兼容性级别150编译现在将语句的执行推迟到第一次执行。这意味着它将不再受零行估计问题的困扰。
没有列统计信息
具有更准确的表基数并不意味着估计的行数将更加准确（除非这样做表中所有行的操作）。 SQL Server完全不维护表变量的列统计信息，因此将基于比较谓词进行猜测（例如，对于非唯一列，对于
sys.partitions将返回表的10％，对于@table_variable比较将返回表的10％） 。相反，为recompile表维护了列统计信息。SQL Server维护对每个列进行的修改数量的计数。如果自计划编译以来的修改次数超过了重新编译阈值（RT），则将重新编译计划并更新统计信息。 RT取决于表的类型和大小。
来自SQL Server 2008中的计划缓存
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注释）
可以使用
=提示将>表的RT设置为与永久表相同。所有这些的最终结果是，当涉及许多行作为SQL Server时，
#temp表生成的执行计划通常比KEEP PLAN更好。 NB1：表变量没有统计信息，但仍会在跟踪标志2453下引发“ Statistics Changed”重新编译事件（不适用于“琐碎”的计划）。与上面的临时表所示相同的重新编译阈值，如果
#temp则附加一个阈值。即，在表变量为空时编译的所有语句最终将在非空时首次执行时得到重新编译并更正。编译时间表的基数存储在计划中，并且如果以相同的基数再次执行该语句（由于控制语句流或缓存的计划的重用），则不会重新编译。NB2：对于缓存的临时表在存储过程中，重新编译的过程比上面描述的要复杂得多。有关所有详细信息，请参见存储过程中的临时表。
重新编译
除了上面描述的基于修改的重新编译之外，
#temp表还可以与其他编译相关联，这仅仅是因为它们允许表变量禁止触发编译的操作（例如DDL更改@table_variables，N=0 -> RT = 1）锁定
已经指出，表变量不参与锁定。不是这种情况。将以下输出运行到SSMS消息选项卡，以获取针对插入语句获取和释放的锁的详细信息。隐式
TableCardinality提示。如下所示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)
### Output
DECLARE @T TABLE(X INT);
SELECT X
FROM @T
OPTION (RECOMPILE, QUERYTRACEON 3604, QUERYTRACEON 8607)
但是，这对锁定的影响可能很小。返回索引索引顺序的结果，指示SQL Server两者都使用了分配顺序的扫描。
我两次运行了上面的脚本，第二次运行的结果在下面。
*** Output Tree: (trivial plan) ***
PhyOp_TableScan TBL: @T Bmk ( Bmk1000) IsRow: COL: IsBaseRow1002 Hints( NOLOCK )
由于SQL Server只是在对象上获得了架构稳定性锁，因此表变量确实极小。但是对于
#temp表，它几乎是轻巧的，因为它取出了对象级CREATE INDEX锁。当同时使用ALTER TABLE表时，当然也可以显式指定SELECT提示或NOLOCK隔离级别。与记录周围的用户事务的问题类似，这意味着对
#temp表的锁定时间更长。使用下面的脚本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
，在两种情况下都在显式用户事务之外运行时，检查
S时返回的唯一锁是NOLOCK上的共享锁。取消注释
READ UNCOMMITTED时，将返回26行，这表明在对象本身和系统表行上都持有锁，以允许回滚并防止其他事务读取未提交的数据。等效的表变量操作不随用户事务而回滚，并且不需要持有这些锁以供我们检入下一条语句，但是跟踪在Profiler中获取和释放的锁或使用跟踪标志1200仍显示大量锁事件仍然存在索引
对于SQL Server 2014之前的版本，只能在表变量上隐式创建索引，这是添加唯一约束或主键的副作用。当然，这确实意味着仅支持唯一索引。可以模拟具有唯一聚集索引的表上的非唯一非聚集索引，方法是简单地将其声明为
#temp并将CI键添加到所需NCI键的末尾（SQL Server会在幕后这样做，即使非如前所述，可以在约束声明中指定各种#temp，包括sys.dm_tran_locks，DATABASE和BEGIN TRAN ... ROLLBACK（尽管没有必要设置一个，因为它只会对索引重建产生任何影响）并且您无法在表变量上重建索引！）另外，表变量不支持
UNIQUE NONCLUSTERED d列，过滤索引（直到2016年）或分区，index_option表却不支持（必须在DATA_COMPRESSION中创建分区方案）。 br /> SQL Server 2014中的索引非唯一索引可以在SQL Server 2014中的表变量定义中内联声明。有关此示例的语法如下。
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 2016中的索引
--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
并行性
插入（或以其他方式修改）
IGNORE_DUP_KEY的查询不能有并行计划，FILLFACTOR不受这种限制。有一个明显的变通办法是，如下重写可以使
INCLUDE部分采用并行放置，但最终使用隐藏的临时表（在幕后）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*/
);
从表变量中选择的查询中没有这样的限制，如我在此处的答案所示
差异
#temp不能在函数内部使用。 tempdb可以在标量或多语句表UDF中使用。@table_variables不能具有命名约束。#temp_tables不能是SELECT -ed #temp_tables，@table_variables -ed，@table_variables d或是@table_variables命令（例如SELECT或INTO）的目标，并且不支持表提示（例如ALTER）。优化器未考虑对表变量进行约束以简化，隐含谓词或矛盾检测。表变量似乎不适合行集共享优化，这意味着针对这些变量的删除和更新计划可能会遇到更多开销，并且需要等待。 （示例）
仅存储？
如开头所述，它们都存储在
TRUNCATE的页面上。但是，在将这些页面写入光盘时，我没有解决行为上是否有任何区别。我对此进行了少量测试，到目前为止，还没有发现任何区别。在我对SQL Server 250页面实例进行的特定测试中，似乎是写入数据文件之前的切入点。
NB：以下行为在SQL Server 2014中不再发生或SQL
Server 2012 SP1 / CU10或SP2 / CU1急切的编写器不再像
急切地将页面写入光盘一样。有关此更改的更多详细信息，请参见SQL
Server 2014：tempdb隐藏性能宝石。
运行以下脚本
DECLARE @T TABLE
(
c1 INT NULL INDEX ix UNIQUE WHERE c1 IS NOT NULL /*Unique ignoring nulls*/
)
，并使用Process Monitor监视对
DBCC数据文件的写入，我看不到（除了偶尔在偏移量73,728处的数据库启动页面上看到）。将DBCC CHECKIDENT更改为SET IDENTITY INSERT后，我开始看到如下所示的写操作。使用表变量进行测试时，我的截断点仍为250页。下面的脚本通过查看WITH (FORCESCAN) INSERT INTO @DATA ( ... )
EXEC('SELECT .. FROM ...')
### Results
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
显示了不同的方式，表明已将192页写入磁盘并清除了脏标志。它还表明，写入磁盘并不意味着页面将立即从缓冲池中退出。仍然可以完全从内存中满足对该表变量的查询。批量记录1,000行/页，并记录每次迭代。
DECLARE @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
表变量和
CHECK表都给出了几乎相同的图形，并设法在缓冲池达到最大值之前达到最大值它们并没有完全保留在内存中，因此似乎对可以消耗多少内存没有特别的限制。
评论
我发现与临时表变量相比，SQL Server在创建临时表（甚至具有缓存）时获得的闩锁数量要多得多。您可以通过使用闩锁获取的调试XE并创建一个包含约35列的表来进行测试。我发现表变量占用4个锁存器，而临时表占用70个左右锁存器。
– Joe Obbish
18年8月29日在17:52
#2 楼
我想指出的几件事更多是基于特定的经验而非学习。作为一名DBA，我是一个新手，因此请在需要时进行纠正。默认情况下，＃temp表使用SQL Server实例的默认排序规则。因此，除非另外指定，否则如果masterdb与数据库的排序规则不同，则可能会在比较或更新#temp表和数据库表之间的值时遇到问题。请参阅：http://www.mssqltips.com/sqlservertip/2440/create-sql-server-temporary-tables-with-the-correct-collation/完全基于个人经验，可用记忆似乎会影响其性能。 MSDN建议使用表变量来存储较小的结果集，但是在大多数情况下，差异甚至不明显。但是，在较大的集合中，在某些情况下，表变量的内存消耗变得非常明显，并且可能使查询变慢以进行爬网。
评论
另请注意，如果您使用的是SQL Server 2012并且包含数据库，则#temp表上的排序规则可以继承调用数据库的排序规则。
–亚伦·伯特兰（Aaron Bertrand）
2013年1月29日19:51
小集大型集＃2的澄清stackoverflow.com/a/14465163/5224021
–有趣的名称-这里
17年2月8日14:56
评论
有关堆栈溢出的相关问题：什么时候应该在SQL Server中使用表变量与临时表？