SQL Server中的临时表和表变量有什么区别?

临时表(#tmp)和表变量(@tmp)之间有一些区别,尽管使用tempdb不是其中之一,如下面MSDN链接中所述。

根据经验,对于中小型数据量和简单的使用场景,您应该使用表变量。(这是一个过于宽泛的指导方针,当然也有很多例外——见下文和后续文章。)

在两者之间做出选择时需要考虑的几点:

• 临时表是真实的表,因此您可以执行CREATE INDEX等操作。如果您有大量数据,通过索引访问这些数据会更快,那么临时表是一个不错的选择。

• 通过使用PRIMARY KEY或UNIQUE约束,表变量可以具有索引。(如果你想要一个非唯一索引,只需将主键列作为唯一约束中的最后一列。如果你没有唯一列,你可以使用标识列。) SQL 2014 has non-unique indexes too .

• 表变量不参与事务和 SELECT s隐含地与 NOLOCK 。事务行为可能非常有用,例如,如果您想在过程中途回滚,那么在该事务期间填充的表变量仍将被填充!

• 临时表可能会导致存储过程被重新编译,这可能是经常发生的。表变量不会。

• 您可以使用SELECT INTO创建临时表,这可以更快地编写(适用于即席查询),并允许您处理随时间变化的数据类型,因为您不需要预先定义临时表结构。

• 您可以从函数传递回表变量,使您能够更容易地封装和重用逻辑(例如,使函数将字符串拆分为某个任意分隔符上的值表)。

• 在用户定义的函数中使用表变量可以更广泛地使用这些函数(有关详细信息,请参阅CREATEFUNCTION文档)。如果你正在编写一个函数,除非有迫切需要,否则你应该使用表变量而不是临时表。

• 表变量和临时表都存储在tempdb中。但是表变量(自2005年以来)默认为当前数据库的排序规则,而临时表则采用tempdb的默认排序规则( ref ).这意味着,如果使用临时表,并且您的数据库排序规则与tempdb不同,您应该注意排序规则问题,如果您想将临时表中的数据与数据库中的数据进行比较,则会导致问题。

• 全局临时表(##tmp)是另一种可供所有会话和用户使用的临时表。

进一步阅读:

• Martin Smith's great answer 在dba.stackexchange.com上

• MSDN常见问题解答:两者之间的区别: https://support.microsoft.com/en-gb/kb/305977

• MDSN博客文章: https://learn.microsoft.com/archive/blogs/sqlserverstorageengine/tempdb-table-variable-vs-local-temporary-table

• 文章: https://searchsqlserver.techtarget.com/tip/Temporary-tables-in-SQL-Server-vs-table-variables

• 临时表和临时变量的意外行为和性能影响: Paul White on SQLblog.com

只需看看公认答案中的声明,即表变量不参与日志记录。

测井数量存在任何差异(至少 insert / update / delete 尽管我有 since found 由于额外的系统表更新,存储过程中缓存的临时对象在这方面存在一些小差异)。

我观察了伐木行为与 @table_variable 以及a #temp 表中列出了以下操作。

1. 插入成功
2. 多行插入语句因违反约束而回滚的位置。
3. 更新
4. 删去
5. 解除分配

所有操作的事务日志记录几乎相同。

表变量版本实际上有几个 额外的 日志条目,因为它会将条目添加到(稍后从中删除) sys.syssingleobjrefs 基表,但总体而言,记录的字节数较少,纯粹是因为表变量的内部名称比 #温度 表(少118个 nvarchar 字符)。

要重现的完整脚本(最好在单用户模式下启动的实例上运行,并使用 sqlcmd 模式)

:setvar tablename "@T" 
:setvar tablescript "DECLARE @T TABLE"

/*
 --Uncomment this section to test a #temp table
:setvar tablename "#T" 
:setvar tablescript "CREATE TABLE #T"
*/

USE tempdb 
GO    
CHECKPOINT

DECLARE @LSN NVARCHAR(25)

SELECT @LSN = MAX([Current LSN])
FROM fn_dblog(null, null) 


EXEC(N'BEGIN TRAN StartBatch
SAVE TRAN StartBatch
COMMIT

$(tablescript)
(
[4CA996AC-C7E1-48B5-B48A-E721E7A435F0] INT PRIMARY KEY DEFAULT 0,
InRowFiller char(7000) DEFAULT ''A'',
OffRowFiller varchar(8000) DEFAULT REPLICATE(''B'',8000),
LOBFiller varchar(max) DEFAULT REPLICATE(cast(''C'' as varchar(max)),10000)
)


BEGIN TRAN InsertFirstRow
SAVE TRAN InsertFirstRow
COMMIT

INSERT INTO $(tablename)
DEFAULT VALUES

BEGIN TRAN Insert9Rows
SAVE TRAN Insert9Rows
COMMIT


INSERT INTO $(tablename) ([4CA996AC-C7E1-48B5-B48A-E721E7A435F0])
SELECT TOP 9 ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM sys.all_columns

BEGIN TRAN InsertFailure
SAVE TRAN InsertFailure
COMMIT


/*Try and Insert 10 rows, the 10th one will cause a constraint violation*/
BEGIN TRY
INSERT INTO $(tablename) ([4CA996AC-C7E1-48B5-B48A-E721E7A435F0])
SELECT TOP (10) (10 + ROW_NUMBER() OVER (ORDER BY (SELECT 0))) % 20
FROM sys.all_columns
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
END CATCH

BEGIN TRAN Update10Rows
SAVE TRAN Update10Rows
COMMIT

UPDATE $(tablename)
SET InRowFiller = LOWER(InRowFiller),
    OffRowFiller  =LOWER(OffRowFiller),
    LOBFiller  =LOWER(LOBFiller)


BEGIN TRAN Delete10Rows
SAVE TRAN Delete10Rows
COMMIT

DELETE FROM  $(tablename)
BEGIN TRAN AfterDelete
SAVE TRAN AfterDelete
COMMIT

BEGIN TRAN EndBatch
SAVE TRAN EndBatch
COMMIT')


DECLARE @LSN_HEX NVARCHAR(25) = 
        CAST(CAST(CONVERT(varbinary,SUBSTRING(@LSN, 1, 8),2) AS INT) AS VARCHAR) + ':' +
        CAST(CAST(CONVERT(varbinary,SUBSTRING(@LSN, 10, 8),2) AS INT) AS VARCHAR) + ':' +
        CAST(CAST(CONVERT(varbinary,SUBSTRING(@LSN, 19, 4),2) AS INT) AS VARCHAR)        

SELECT 
    [Operation],
    [Context],
    [AllocUnitName],
    [Transaction Name],
    [Description]
FROM   fn_dblog(@LSN_HEX, null) AS D
WHERE  [Current LSN] > @LSN  

SELECT CASE
         WHEN GROUPING(Operation) = 1 THEN 'Total'
         ELSE Operation
       END AS Operation,
       Context,
       AllocUnitName,
       COALESCE(SUM([Log Record Length]), 0) AS [Size in Bytes],
       COUNT(*)                              AS Cnt
FROM   fn_dblog(@LSN_HEX, null) AS D
WHERE  [Current LSN] > @LSN  
GROUP BY GROUPING SETS((Operation, Context, AllocUnitName),())

结果

+-----------------------+--------------------+---------------------------+---------------+------+---------------+------+------------------+
|                       |                    |                           |             @TV      |             #TV      |                  |
+-----------------------+--------------------+---------------------------+---------------+------+---------------+------+------------------+
| Operation             | Context            | AllocUnitName             | Size in Bytes | Cnt  | Size in Bytes | Cnt  | Difference Bytes |
+-----------------------+--------------------+---------------------------+---------------+------+---------------+------+------------------+
| LOP_ABORT_XACT        | LCX_NULL           |                           | 52            | 1    | 52            | 1    |                  |
| LOP_BEGIN_XACT        | LCX_NULL           |                           | 6056          | 50   | 6056          | 50   |                  |
| LOP_COMMIT_XACT       | LCX_NULL           |                           | 2548          | 49   | 2548          | 49   |                  |
| LOP_COUNT_DELTA       | LCX_CLUSTERED      | sys.sysallocunits.clust   | 624           | 3    | 624           | 3    |                  |
| LOP_COUNT_DELTA       | LCX_CLUSTERED      | sys.sysrowsets.clust      | 208           | 1    | 208           | 1    |                  |
| LOP_COUNT_DELTA       | LCX_CLUSTERED      | sys.sysrscols.clst        | 832           | 4    | 832           | 4    |                  |
| LOP_CREATE_ALLOCCHAIN | LCX_NULL           |                           | 120           | 3    | 120           | 3    |                  |
| LOP_DELETE_ROWS       | LCX_INDEX_INTERIOR | Unknown Alloc Unit        | 720           | 9    | 720           | 9    |                  |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysallocunits.clust   | 444           | 3    | 444           | 3    |                  |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysallocunits.nc      | 276           | 3    | 276           | 3    |                  |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.syscolpars.clst       | 628           | 4    | 628           | 4    |                  |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.syscolpars.nc         | 484           | 4    | 484           | 4    |                  |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysidxstats.clst      | 176           | 1    | 176           | 1    |                  |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysidxstats.nc        | 144           | 1    | 144           | 1    |                  |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysiscols.clst        | 100           | 1    | 100           | 1    |                  |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysiscols.nc1         | 88            | 1    | 88            | 1    |                  |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysobjvalues.clst     | 596           | 5    | 596           | 5    |                  |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysrowsets.clust      | 132           | 1    | 132           | 1    |                  |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysrscols.clst        | 528           | 4    | 528           | 4    |                  |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysschobjs.clst       | 1040          | 6    | 1276          | 6    | 236              |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysschobjs.nc1        | 820           | 6    | 1060          | 6    | 240              |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysschobjs.nc2        | 820           | 6    | 1060          | 6    | 240              |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.sysschobjs.nc3        | 480           | 6    | 480           | 6    |                  |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.syssingleobjrefs.clst | 96            | 1    |               |      | -96              |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | sys.syssingleobjrefs.nc1  | 88            | 1    |               |      | -88              |
| LOP_DELETE_ROWS       | LCX_MARK_AS_GHOST  | Unknown Alloc Unit        | 72092         | 19   | 72092         | 19   |                  |
| LOP_DELETE_ROWS       | LCX_TEXT_MIX       | Unknown Alloc Unit        | 16348         | 37   | 16348         | 37   |                  |
| LOP_FORMAT_PAGE       | LCX_HEAP           | Unknown Alloc Unit        | 1596          | 19   | 1596          | 19   |                  |
| LOP_FORMAT_PAGE       | LCX_IAM            | Unknown Alloc Unit        | 252           | 3    | 252           | 3    |                  |
| LOP_FORMAT_PAGE       | LCX_INDEX_INTERIOR | Unknown Alloc Unit        | 84            | 1    | 84            | 1    |                  |
| LOP_FORMAT_PAGE       | LCX_TEXT_MIX       | Unknown Alloc Unit        | 4788          | 57   | 4788          | 57   |                  |
| LOP_HOBT_DDL          | LCX_NULL           |                           | 108           | 3    | 108           | 3    |                  |
| LOP_HOBT_DELTA        | LCX_NULL           |                           | 9600          | 150  | 9600          | 150  |                  |
| LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.sysallocunits.clust   | 456           | 3    | 456           | 3    |                  |
| LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.syscolpars.clst       | 644           | 4    | 644           | 4    |                  |
| LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.sysidxstats.clst      | 180           | 1    | 180           | 1    |                  |
| LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.sysiscols.clst        | 104           | 1    | 104           | 1    |                  |
| LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.sysobjvalues.clst     | 616           | 5    | 616           | 5    |                  |
| LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.sysrowsets.clust      | 136           | 1    | 136           | 1    |                  |
| LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.sysrscols.clst        | 544           | 4    | 544           | 4    |                  |
| LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.sysschobjs.clst       | 1064          | 6    | 1300          | 6    | 236              |
| LOP_INSERT_ROWS       | LCX_CLUSTERED      | sys.syssingleobjrefs.clst | 100           | 1    |               |      | -100             |
| LOP_INSERT_ROWS       | LCX_CLUSTERED      | Unknown Alloc Unit        | 135888        | 19   | 135888        | 19   |                  |
| LOP_INSERT_ROWS       | LCX_INDEX_INTERIOR | Unknown Alloc Unit        | 1596          | 19   | 1596          | 19   |                  |
| LOP_INSERT_ROWS       | LCX_INDEX_LEAF     | sys.sysallocunits.nc      | 288           | 3    | 288           | 3    |                  |
| LOP_INSERT_ROWS       | LCX_INDEX_LEAF     | sys.syscolpars.nc         | 500           | 4    | 500           | 4    |                  |
| LOP_INSERT_ROWS       | LCX_INDEX_LEAF     | sys.sysidxstats.nc        | 148           | 1    | 148           | 1    |                  |
| LOP_INSERT_ROWS       | LCX_INDEX_LEAF     | sys.sysiscols.nc1         | 92            | 1    | 92            | 1    |                  |
| LOP_INSERT_ROWS       | LCX_INDEX_LEAF     | sys.sysschobjs.nc1        | 844           | 6    | 1084          | 6    | 240              |
| LOP_INSERT_ROWS       | LCX_INDEX_LEAF     | sys.sysschobjs.nc2        | 844           | 6    | 1084          | 6    | 240              |
| LOP_INSERT_ROWS       | LCX_INDEX_LEAF     | sys.sysschobjs.nc3        | 504           | 6    | 504           | 6    |                  |
| LOP_INSERT_ROWS       | LCX_INDEX_LEAF     | sys.syssingleobjrefs.nc1  | 92            | 1    |               |      | -92              |
| LOP_INSERT_ROWS       | LCX_TEXT_MIX       | Unknown Alloc Unit        | 5112          | 71   | 5112          | 71   |                  |
| LOP_MARK_SAVEPOINT    | LCX_NULL           |                           | 508           | 8    | 508           | 8    |                  |
| LOP_MODIFY_COLUMNS    | LCX_CLUSTERED      | Unknown Alloc Unit        | 1560          | 10   | 1560          | 10   |                  |
| LOP_MODIFY_HEADER     | LCX_HEAP           | Unknown Alloc Unit        | 3780          | 45   | 3780          | 45   |                  |
| LOP_MODIFY_ROW        | LCX_CLUSTERED      | sys.syscolpars.clst       | 384           | 4    | 384           | 4    |                  |
| LOP_MODIFY_ROW        | LCX_CLUSTERED      | sys.sysidxstats.clst      | 100           | 1    | 100           | 1    |                  |
| LOP_MODIFY_ROW        | LCX_CLUSTERED      | sys.sysrowsets.clust      | 92            | 1    | 92            | 1    |                  |
| LOP_MODIFY_ROW        | LCX_CLUSTERED      | sys.sysschobjs.clst       | 1144          | 13   | 1144          | 13   |                  |
| LOP_MODIFY_ROW        | LCX_IAM            | Unknown Alloc Unit        | 4224          | 48   | 4224          | 48   |                  |
| LOP_MODIFY_ROW        | LCX_PFS            | Unknown Alloc Unit        | 13632         | 169  | 13632         | 169  |                  |
| LOP_MODIFY_ROW        | LCX_TEXT_MIX       | Unknown Alloc Unit        | 108640        | 120  | 108640        | 120  |                  |
| LOP_ROOT_CHANGE       | LCX_CLUSTERED      | sys.sysallocunits.clust   | 960           | 10   | 960           | 10   |                  |
| LOP_SET_BITS          | LCX_GAM            | Unknown Alloc Unit        | 1200          | 20   | 1200          | 20   |                  |
| LOP_SET_BITS          | LCX_IAM            | Unknown Alloc Unit        | 1080          | 18   | 1080          | 18   |                  |
| LOP_SET_BITS          | LCX_SGAM           | Unknown Alloc Unit        | 120           | 2    | 120           | 2    |                  |
| LOP_SHRINK_NOOP       | LCX_NULL           |                           |               |      | 32            | 1    | 32               |
+-----------------------+--------------------+---------------------------+---------------+------+---------------+------+------------------+
| Total                 |                    |                           | 410144        | 1095 | 411232        | 1092 | 1088             |
+-----------------------+--------------------+---------------------------+---------------+------+---------------+------+------------------+

在哪些情况下,一种表现优于另一种?

对于较小的表(少于1000行),使用临时变量,否则使用临时表。

@wcm-实际上,挑剔表变量不仅仅是Ram-它可以部分存储在磁盘上。

临时表可以有索引,而表变量只能有主索引。如果速度是一个问题,表变量可以更快,但很明显,如果有很多记录,或者需要搜索聚集索引的临时表,那么临时表会更好。

Good background article

1. 临时表:临时表易于创建和备份数据。

   表变量:但是表变量涉及我们通常创建普通表时的工作量。

2. 温度表:温度表结果可供多个用户使用。

   表变量:但表变量只能由当前用户使用。

3. 温度表:温度表将存储在tempdb中。它将产生网络流量。当临时表中有大量数据时,它必须在整个数据库中工作。将存在性能问题。

   表变量:但是表变量会将一些数据存储在物理内存中,然后当大小增加时,它会被移动到tempdb中。

4. Temp-table:Temp-table可以执行所有DDL操作。它允许创建索引、删除、更改等。。,

   表变量:而表变量不允许执行DDL操作。但是表变量只允许我们创建聚集索引。

5. 临时表:临时表可用于当前会话或全局。以便多用户会话可以利用表中的结果。

   表变量:但是表变量可以用于该程序。(存储过程)

6. 临时表:临时变量不能使用事务。当我们对临时表执行DML操作时,可以回滚或提交事务。

   表变量:但我们不能对表变量执行此操作。

7. 温度表:函数不能使用温度变量。此外,我们不能在函数中执行DML操作。

   表变量:但是函数允许我们使用表变量。但是使用表变量我们可以做到这一点。

8. 临时表:当我们为每个后续调用使用临时变量时,存储过程将进行重新编译(不能使用相同的执行计划)。

   表变量:然而表变量不会这样做。

对于所有相信临时变量只存在于内存中的神话的人

首先,表变量不一定是内存驻留的。在内存压力下,属于表变量的页面可以被推送到tempdb。

请在此处阅读文章: TempDB:: Table variable vs local temporary table

引自:; Professional SQL Server 2012 Internals and Troubleshooting

统计数字 临时表和表变量之间的主要区别在于 不会对表变量创建统计信息。这有两个主要方面 后果,首先是查询优化器使用 表变量行数的固定估计 而不管它包含什么数据。此外,添加或删除 数据不会改变估计。

指数 虽然可以,但您不能对表变量创建索引 创建约束。这意味着通过创建主键或唯一 约束,您可以有索引(因为创建这些索引是为了支持 约束)表变量。即使你有限制 因此,具有统计信息的索引将不会 在编译查询时使用,因为它们在编译时不存在 时间,也不会造成重新计算。

架构修改 临时架构修改是可能的 表,但不在表变量上。虽然模式修改是 可能在临时表上,避免使用它们,因为它们会导致 重新编译使用这些表的语句。

表变量未在内存中创建

有一种常见的误解,认为表变量在内存结构中 因此,其执行速度将快于临时表 感谢DMV 称为sys。dm-db会话空间使用情况,通过以下方式显示tempdb使用情况 会话, 你可以证明事实并非如此 。重新启动SQL Server以清除 DMV,运行以下脚本以确认您的会话id为返回0 用户_对象_分配_页面_计数:

SELECT session_id,
database_id,
user_objects_alloc_page_count
FROM sys.dm_db_session_space_usage
WHERE session_id > 50 ;

现在,您可以通过运行以下命令来检查临时表使用了多少空间 创建一个包含一列的临时表并用一行填充它的脚本:

CREATE TABLE #TempTable ( ID INT ) ;
INSERT INTO #TempTable ( ID )
VALUES ( 1 ) ;
GO
SELECT session_id,
database_id,
user_objects_alloc_page_count
FROM sys.dm_db_session_space_usage
WHERE session_id > 50 ;

我服务器上的结果表明,该表在tempdb中分配了一个页面。 现在运行相同的脚本,但使用表变量 这次:

DECLARE @TempTable TABLE ( ID INT ) ;
INSERT INTO @TempTable ( ID )
VALUES ( 1 ) ;
GO
SELECT session_id,
database_id,
user_objects_alloc_page_count
FROM sys.dm_db_session_space_usage
WHERE session_id > 50 ;

使用哪一个?

是否使用临时表或表变量应 经过彻底测试,但 最好是暂时的 表作为默认值,因为可以使用的东西要少得多 错误的 .

我见过客户使用表变量开发代码,因为他们 我们正在处理少量的行,而且比 临时桌子,但几年后有数百张 表变量中有数千行,性能很糟糕, 因此,在制定您的计划时,请尝试考虑一些容量规划 决定!

另一个主要区别是表变量没有列统计信息,而临时表则有。这意味着查询优化器不知道表变量中有多少行(它猜测为1),如果表变量实际上有大量行,这可能会导致生成高度非最优的计划。

另一个区别:

只能从创建表的过程中的语句访问表var,而不能从该过程调用的其他过程或嵌套动态SQL(通过exec或sp_executesql)访问表var。

另一方面,临时表的作用域包括调用过程和嵌套动态SQL中的代码。

如果您的过程创建的表必须可以从其他调用过程或动态SQL访问,则必须使用临时表。这在复杂的情况下非常方便。

令我惊讶的是,没有人提到这两者之间的关键区别在于临时表支持 并行插入 而表变量没有。您应该能够看到与执行计划的区别。这是 the video from SQL Workshops on Channel 9 和 MSDN doc .

这也解释了为什么您应该为较小的表使用表变量,否则使用临时表,如 SQLMenace answered 之前。

之间的差异 Temporary Tables (##temp/#temp) 和 Table Variables (@table) 如:

1. Table variable (@table) 创建于 memory 然而,a Temporary table (##temp/#temp) 创建于 tempdb database 然而,如果存在内存压力,则属于表变量的页面可能会被推送到tempdb。

2. Table variables 不能参与 transactions, logging or locking 这使得 @table faster then #temp 因此,表变量比临时表更快。

3. Temporary table 允许对架构进行修改 表变量 .

4. Temporary tables 在创建的例程和子例程中都是可见的。然而,表变量仅在创建的例程中可见。

5. 临时表 被允许 CREATE INDEXes 然而, 表变量 不允许 CREATE INDEX 相反,他们可以通过使用 Primary Key or Unique Constraint .