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

临时表(tmp)和表变量(@tmp)之间存在一些差异,尽管使用tempdb不是其中之一,如下面的msdn链接中所述。

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

在它们之间选择时要考虑的一些要点:

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

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

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

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

• 您可以使用select into创建一个临时表,它可以更快地进行写入(有利于特殊查询),并且可以允许您处理随时间变化的数据类型,因为您不需要预先定义临时表结构。

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

• 在用户定义函数中使用表变量可以使这些函数得到更广泛的使用(有关详细信息,请参见创建函数文档)。如果您正在编写一个函数,那么您应该使用表变量而不是临时表,除非另有迫切需要。

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

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

进一步阅读:

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

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

• MDSN博客文章: http://blogs.msdn.com/sqlserverstorageengine/archive/2008/03/30/sql-server-table-variable-vs-local-temporary-table.aspx

• 文章: http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1267047,00.html#

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

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

似乎普遍不真实的是,测井数量有任何差异(至少 insert / update / delete 表本身的操作 since found 在这方面,由于附加的系统表更新,存储过程中缓存的临时对象存在一些小的差异)。

我观察了两种情况下的日志记录行为 @table_variable 和A #temp 以下操作的表。

1. 插入成功
2. 由于违反约束,多行插入WHERE语句回滚。
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行),使用temp变量,否则使用temp表。

@wcm——实际上,选择表变量并不仅仅是RAM——它可以部分存储在磁盘上。

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

Good background article

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

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

2. 临时表:临时表结果可供多个用户使用。

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

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

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

4. 临时表:临时表可以执行所有DDL操作。它允许创建索引、删除、更改等。

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

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

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

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

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

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

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

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

   表变量:但是表变量不会这样做。

对于所有相信温度变量只存在于记忆中的人来说

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

阅读本文: TempDB:: Table variable vs local temporary table

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

引自; Professional SQL Server 2012 Internals and Troubleshooting

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

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

架构修改 可以临时修改架构 但不在表变量上。尽管模式修改是 可能出现在临时表上,请避免使用它们,因为它们会导致 重新编译使用表的语句。

表变量不是在内存中创建的

有一种常见的误解,即表变量在内存结构中 因此,执行速度比临时表快。 . 多亏了一辆电动车 称为sys。dm?db?session?space?usage,显示tempdb的使用情况 会话, 你可以证明事实并非如此 . 重新启动SQL Server以清除 dmv,运行以下脚本以配置会话id返回0的rm 用户对象分配页面计数:

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 ;

用哪一个?

无论是否使用临时表或表变量,都应该 通过彻底的测试决定,但是 最好是暂时的 表作为默认值,因为可以执行的操作要少得多 错误的 .

我见过客户使用表变量开发代码,因为他们 处理少量的争吵,比 临时桌,但几年后有几百个 表变量中有数千行,性能很差, 因此,当您 决定!

另一个区别是:

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

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

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

另外,您还可以经常用派生表来替换这两个表,派生表也可能更快。但是,与所有性能调优一样,只有针对实际数据的实际测试才能告诉您特定查询的最佳方法。

临时表

临时表的行为类似于实际表,但在运行时创建。它的工作原理类似于真正的桌子。我们几乎可以在实际表中执行所有可能的操作。我们可以在临时表上使用诸如alter、create、drop等DDL语句。

临时表的结构在创建后可能发生任何更改。临时表存储在系统数据库的tempdb数据库中。

临时表参与事务、日志记录或锁定。由于这个原因,它比表变量慢。

表变量

它的变量,但工作起来像一张桌子。它还创建到tempdb数据库中,而不是内存中。表变量仅在批处理或存储过程范围内可用。不需要删除表变量,它在批处理和存储过程执行过程完成时自动删除。

表变量支持主键,创建时标识。但它不支持非聚集索引。在声明主键之后,标识不能修改它们。

表变量不参与事务、日志记录或锁定。事务、日志记录和锁定不影响表变量。

阅读本文了解更多信息- http://goo.gl/GXtXqz