在SQL Server 2005中，我们可以通过以下两种方式之一创建临时表：
1
| declare @tmp table (Col1 int, Col2 int); |
要么
1
| create table #tmp (Col1 int, Col2 int); |
这两者有什么不同？ 关于@tmp是否仍然使用tempdb，或者是否所有内容都发生在内存中，我已经阅读了相互矛盾的意见。
在哪种情况下，一个人胜过另一个？
临时表(#tmp)和表变量(@tmp)之间存在一些差异，尽管使用tempdb不是其中之一，如下面的MSDN链接中所述。
根据经验，对于中小批量数据和简单使用场景，您应该使用表变量。 (这是一个过于宽泛的指导方针，当然有很多例外 - 见下文和以下文章。)
在选择它们时需要考虑的一些要点：
-
临时表是真正的表，因此您可以执行诸如CREATE INDEXes等操作。如果您有大量数据，通过索引访问将更快，那么临时表是一个不错的选择。
-
表变量可以通过使用PRIMARY KEY或UNIQUE约束来获得索引。 (如果您希望非唯一索引只包含主键列作为唯一约束中的最后一列。如果您没有唯一列，则可以使用标识列。)SQL 2014也具有非唯一索引。
-
表变量不参与事务，SELECT与NOLOCK隐式相关。事务行为可能非常有用，例如，如果您想在程序中途进行ROLLBACK，那么仍将填充在该事务期间填充的表变量！
-
临时表可能导致重新编译存储过程，可能经常。表变量不会。
-
您可以使用SELECT INTO创建临时表，这可以更快地编写(适用于临时查询)，并且可以允许您随着时间的推移处理更改的数据类型，因为您不需要预先定义临时表结构。
-
您可以从函数中传回表变量，使您能够更容易地封装和重用逻辑(例如，创建一个函数将字符串拆分为某个任意分隔符上的值表)。
-
在用户定义的函数中使用表变量可以更广泛地使用这些函数(有关详细信息，请参阅CREATE FUNCTION文档)。如果你正在编写一个函数，你应该在临时表中使用表变量，除非有其他令人信服的需求。
-
表变量和临时表都存储在tempdb中。但表变量(自2005年起)默认为当前数据库与临时表的排序规则，临时表采用tempdb(ref)的默认排序规则。这意味着如果使用临时表并且db collat??ion与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操作)存在任何差异似乎通常是不正确的，尽管我已经发现在这方面对于缓存的临时存在一些小的差异由于额外的系统表更新而导致存储过程中的对象)。
我针对@table_variable和#temp表查看了针对以下操作的日志记录行为。
成功插入
多行插入where语句由于约束违规而回滚。
更新
删除
取消分配
所有操作的事务日志记录几乎相同。
表变量版本实际上有一些额外的日志条目，因为它获得了一个条目，该条目被添加到sys.syssingleobjrefs基表中(后来被删除)但总体上记录的字节数少了几个，因为表变量的内部名称消耗了236个字节比#temp表(118个nvarchar个字符)少。
重现的完整脚本(在单用户模式下启动并使用sqlcmd模式的实例上运行最佳)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
| :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),()) |
结果
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
| +-----------------------+--------------------+---------------------------+---------------+------+---------------+------+------------------+
| | | | @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 |
+-----------------------+--------------------+---------------------------+---------------+------+---------------+------+------------------+ |
In which scenarios does one out-perform the other?
对于较小的表(少于1000行)，请使用临时变量，否则使用临时表。
@wcm - 实际上要挑选表变量不仅仅是Ram - 它可以部分存储在磁盘上。
临时表可以有索引，而表变量只能有主索引。如果速度是一个问题表变量可以更快，但显然如果有很多记录，或者需要搜索聚簇索引的临时表，那么临时表会更好。
好的背景文章
临时表：临时表很容易创建和备份数据。
表变量：但是表变量涉及我们通常创建普通表时的工作量。
临时表：临时表结果可供多个用户使用。
表变量：但表变量只能由当前用户使用。
临时表：临时表将存储在tempdb中。它将使网络流量。当我们在临时表中有大量数据时，它必须跨数据库工作。存在性能问题。
表变量：但是表变量将存储在物理内存中的某些数据，然后当大小增加时它将被移动到tempdb。
临时表：临时表可以执行所有DDL操作。它允许创建索引，删除，更改等。，
表变量：表变量不允许执行DDL操作。但是表变量允许我们仅创建聚簇索引。
临时表：临时表可用于当前会话或全局会话。这样多个用户会话就可以利用表中的结果。
表变量：但表变量可以用于该程序。 (存储过程)
临时表：临时变量不能使用事务。当我们使用临时表执行DML操作时，它可以回滚或提交事务。
表变量：但我们不能对表变量这样做。
临时表：函数不能使用临时变量。更多的是我们不能在函数中进行DML操作。
表变量：但该函数允许我们使用表变量。但是使用表变量我们可以做到这一点。
临时表：当我们对每个后续调用使用临时变量时，存储过程将执行重新编译(不能使用相同的执行计划)。
表变量：而表变量不会那样做。
对于所有相信临时变量仅在内存中的神话的人
首先，表变量不一定是内存驻留。在内存压力下，属于表变量的页面可以推送到tempdb。
阅读这里的文章：TempDB :: Table变量vs本地临时表
报价取自;专业SQL Server 2012内部和故障排除
Statistics
The major difference between temp tables and table variables is that
statistics are not created on table variables. This has two major
consequences, the fi rst of which is that the Query Optimizer uses a
fi xed estimation for the number of rows in a table variable
irrespective of the data it contains. Moreover, adding or removing
data doesn’t change the estimation.
Indexes You can’t create indexes on table variables although you can
create constraints. This means that by creating primary keys or unique
constraints, you can have indexes (as these are created to support
constraints) on table variables. Even if you have constraints, and
therefore indexes that will have statistics, the indexes will not be
used when the query is compiled because they won’t exist at compile
time, nor will they cause recompilations.
Schema Modifications Schema modifications are possible on temporary
tables but not on table variables. Although schema modifi cations are
possible on temporary tables, avoid using them because they cause
recompilations of statements that use the tables.
表格变量不是在记忆中创建的
存在一种常见的误解，即表变量是内存中的结构
因此，它比临时表执行得更快。感谢DMV
叫做sys。 dm _ db _ session _ space _ usage，显示tempdb的使用情况
会议，你可以证明事实并非如此。重新启动SQL Server后清除
DMV，运行以下脚本以确认您的session _ id为0返回0
user _ objects _ alloc _ page _ count：
1
2
3
4
5
| SELECT session_id,
database_id,
user_objects_alloc_page_count
FROM sys.dm_db_session_space_usage
WHERE session_id > 50 ; |
现在，您可以通过运行以下内容来检查临时表使用的空间大小
脚本创建一个包含一列的临时表，并用一行填充它：
1
2
3
4
5
6
7
8
9
| 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中分配了一个页面。
现在运行相同的脚本但使用表变量
这次：
1
2
3
4
5
6
7
8
9
| 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 ; |
哪一个使用？
Whether or not you use temporary tables or table variables should be
decided by thorough testing, but it’s best to lean towards temporary
tables as the default because there are far fewer things that can go
wrong.
I’ve seen customers develop code using table variables because they
were dealing with a small amount of rows, and it was quicker than a
temporary table, but a few years later there were hundreds of
thousands of rows in the table variable and performance was terrible,
so try and allow for some capacity planning when you make your
decision!
另一个主要区别是表变量没有列统计信息，而临时表则没有。这意味着查询优化器不知道表变量中有多少行(它猜测为1)，如果表变量实际上有大量行，则可能导致生成高度非最优计划。
另一个区别：
表var只能从创建它的过程中的语句访问，而不能从该过程调用的其他过程或嵌套动态SQL(通过exec或sp_executesql)访问。
另一方面，临时表的作用域包括被调用过程和嵌套动态SQL中的代码。
如果必须可以从其他调用过程或动态SQL访问过程创建的表，则必须使用临时表。这在复杂情况下非常方便。
还要考虑您经常可以用派生表替换它们，这些表也可能更快。但是，与所有性能调整一样，只有针对实际数据的实际测试才能告诉您针对特定查询的最佳方法。
