在SQL Server 2005中,我们可以通过以下两种方式之一创建临时表:
declare @tmp table (Col1 int, Col2 int);
要么
create table #tmp (Col1 int, Col2 int);
这两者有什么不同？关于@tmp是否仍然使用tempdb,或者是否所有内容都发生在内存中,我已经阅读了相互矛盾的意见.
在哪种情况下,一个人胜过另一个？
临时表(#tmp)和表变量(@tmp)之间存在一些差异,尽管使用tempdb不是其中之一,如下面的MSDN链接中所述.
根据经验,对于中小批量数据和简单的使用场景,您应该使用表变量.(这是一个过于宽泛的指导方针,当然有很多例外 - 见下文和以下文章.)
在选择它们时需要考虑的一些要点:
临时表是真正的表,因此您可以执行诸如CREATE INDEXes等操作.如果您有大量数据,通过索引访问将更快,那么临时表是一个不错的选择.
表变量可以通过使用PRIMARY KEY或UNIQUE约束来获得索引.(如果您希望非唯一索引只包含主键列作为唯一约束中的最后一列.如果您没有唯一列,则可以使用标识列.)SQL 2014也具有非唯一索引.
表变量不参与事务,而SELECTs隐式具有NOLOCK.事务行为可能非常有用,例如,如果您想在程序中途进行ROLLBACK,那么在该事务期间填充的表变量仍将被填充!
临时表可能导致重新编译存储过程,可能经常.表变量不会.
您可以使用SELECT INTO创建临时表,这可以更快地编写(适用于临时查询),并且可以允许您随着时间的推移处理更改的数据类型,因为您不需要预先定义临时表结构.
您可以从函数中传回表变量,使您能够更容易地封装和重用逻辑(例如,创建一个函数将字符串拆分为某个任意分隔符上的值表).
在用户定义的函数中使用表变量可以更广泛地使用这些函数(有关详细信息,请参阅CREATE FUNCTION文档).如果你正在编写一个函数,你应该在临时表中使用表变量,除非有其他令人信服的需求.
表变量和临时表都存储在tempdb中.但表变量(自2005年起)默认为当前数据库与临时表的排序规则,临时表采用tempdb(ref)的默认排序规则.这意味着如果使用临时表并且db collation与tempdb不同,则应该了解归类问题,如果要将临时表中的数据与数据库中的数据进行比较,则会导致问题.
全局临时表(## tmp)是可用于所有会话和用户的另一种临时表.
进一步阅读:
Martin Smith在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在SQLblog.com上
只是在接受的答案中查看表变量不参与日志记录的声明.
一般来说,日志记录的数量存在任何差异(至少对于表本身的insert/ update/ delete操作)似乎通常是不正确的,尽管我已经发现由于额外的系统表,存储过程中的缓存临时对象在这方面存在一些小的差别更新).
我查看了针对a @table_variable和#temptable 的日志记录行为,以进行以下操作.
成功插入
多行插入where语句由于约束违规而回滚.
更新
删除
取消分配
所有操作的事务日志记录几乎相同.
表变量版本实际上有一些额外的日志条目,因为它获得了一个添加到(后来从中删除)sys.syssingleobjrefs基表的条目,但整体上记录的字节数少了几个,因为表变量的内部名称比#temp表格少了236个字节(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 - 它可以部分存储在磁盘上.
临时表可以有索引,而表变量只能有主索引.如果速度是一个问题表变量可以更快,但显然如果有很多记录,或者需要搜索聚簇索引的临时表,那么临时表会更好.
好的背景文章
临时表:临时表很容易创建和备份数据.
表变量:但是表变量涉及我们通常创建普通表时的工作量.
临时表:临时表结果可供多个用户使用.
表变量:但表变量只能由当前用户使用.
临时表:临时表将存储在tempdb中.它将使网络流量.当我们在临时表中有大量数据时,它必须跨数据库工作.存在性能问题.
表变量:但是表变量将存储在物理内存中的某些数据,然后当大小增加时它将被移动到tempdb.
临时表:临时表可以执行所有DDL操作.它允许创建索引,删除,更改等.,
表变量:表变量不允许执行DDL操作.但是表变量允许我们仅创建聚簇索引.
临时表:临时表可用于当前会话或全局会话.这样多个用户会话就可以利用表中的结果.
表变量:但表变量可以用于该程序.(存储过程)
临时表:临时变量不能使用事务.当我们使用临时表执行DML操作时,它可以回滚或提交事务.
表变量:但我们不能对表变量这样做.
临时表:函数不能使用临时变量.更多的是我们不能在函数中进行DML操作.
表变量:但该函数允许我们使用表变量.但是使用表变量我们可以做到这一点.
临时表:当我们对每个后续调用使用临时变量时,存储过程将执行重新编译(不能使用相同的执行计划).
表变量:而表变量不会那样做.
对于所有相信临时变量仅在内存中的神话的人
首先,表变量不一定是内存驻留.在内存压力下,属于表变量的页面可以推送到tempdb.
阅读这里的文章:TempDB :: Table变量vs本地临时表
另一个主要区别是表变量没有列统计信息,而临时表则没有.这意味着查询优化器不知道表变量中有多少行(它猜测为1),如果表变量实际上有大量行,则可能导致生成高度非最优计划.
报价取自; 专业SQL Server 2012内部和故障排除
统计信息 临时表和表变量之间的主要区别是不会在表变量上创建统计信息.这有两个主要结果,第一个结果是查询优化器对表变量中的行数使用固定估计,而不管它包含的数据.此外,添加或删除数据不会改变估计.
索引尽管可以创建约束,但无法在表变量上创建索引.这意味着通过创建主键或唯一约束,您可以在表变量上创建索引(因为这些是为了支持约束而创建的).即使您有约束,因此索引将具有统计信息,编译查询时也不会使用索引,因为它们在编译时不存在,也不会导致重新编译.
模式修改模式修改可以在临时表上进行,但不能在表变量上进行.尽管可以在临时表上进行模式修改,但请避免使用它们,因为它们会导致重新编译使用表的语句.
表格变量不是在记忆中创建的
有一种常见的误解,表变量是内存中的结构,因此比临时表执行得更快.感谢称为sys的DMV.dm _ db _ session _ space _用法,它显示会话的tempdb使用情况,你可以证明并非如此.重新启动SQL Server以清除DMV后,运行以下脚本以确认您的session _ id为用户_对象返回0 _ alloc _ page _ count:
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 ;
哪一个使用？
是否使用临时表或表变量应该通过彻底的测试来确定,但最好是将临时 表作为默认表,因为可能 出错的事情要少得多.
我见过客户使用表变量开发代码,因为它们处理少量行,并且它比临时表更快,但几年后表变量中有数十万行,性能很糟糕,所以在做出决定时尝试并允许一些容量规划!
京公网安备 11010802040832号 | 京ICP备19059560号-6 