: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),())
There is a common misconception that table variables are in-memory structures and as such will perform quicker than temporary tables . 感谢称为sys的DMV . dm _ db _ session _ space _ usage，显示会话的tempdb使用情况， you can prove that’s not the case . 重新启动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 ;
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 ;
11 回答
临时表（#tmp）和表变量（@tmp）之间存在一些差异，尽管使用tempdb不是其中之一，如下面的MSDN链接中所述 .
根据经验，对于中小批量数据和简单的使用场景，您应该使用表变量 . （这是一个过于宽泛的指导方针，当然有很多例外 - 见下文和后续文章 . ）
在选择它们时要考虑的一些要点：
临时表是真实表，因此您可以执行CREATE INDEXes等操作 . 如果您有大量数据，通过索引访问将更快，那么临时表是一个不错的选择 .
表变量可以通过使用PRIMARY KEY或UNIQUE约束来获得索引 . （如果您想要一个非唯一索引，只需将主键列包含在唯一约束中的最后一列 . 如果您没有唯一列，则可以使用标识列 . ）SQL 2014 has non-unique indexes too .
表变量不参与事务，而
SELECT是隐含的NOLOCK. 事务行为可能非常有用，例如，如果您想在程序中途进行ROLLBACK，那么仍将填充在该事务期间填充的表变量！临时表可能导致重新编译存储过程，可能经常 . 表变量不会 .
您可以使用SELECT INTO创建临时表，可以更快地编写（适用于临时查询），并且可以允许您随着时间的推移处理不断变化的数据类型，因为您不需要预先定义临时表结构 .
您可以从函数中传回表变量，使您能够更容易地封装和重用逻辑（例如，创建一个函数将字符串拆分为某个任意分隔符上的值表） .
在用户定义的函数中使用表变量可以更广泛地使用这些函数（有关详细信息，请参阅CREATE FUNCTION文档） . 如果你正在编写一个函数，你应该在临时表中使用表变量，除非有其他令人信服的需求 .
表变量和临时表都存储在tempdb中 . 但表变量（自2005年起）默认为当前数据库与临时表的排序规则，临时表采用tempdb（ref）的默认排序规则 . 这意味着如果使用临时表并且db collation与tempdb不同，则应该了解归类问题，如果要将临时表中的数据与数据库中的数据进行比较，则会导致问题 .
全局临时表（## tmp）是可用于所有会话和用户的另一种临时表 .
进一步阅读：
ba9 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和#temp表的日志记录行为 .成功插入
多行插入where语句由于约束违规而回滚 .
更新
删除
Deallocate
所有操作的事务日志记录几乎相同 .
表变量版本实际上有一些 extra 日志条目，因为它获得了一个条目，该条目被添加到
sys.syssingleobjrefs基表中（后来被删除）但总体上记录的字节数少了几个，因为表变量的内部名称比字节变量消耗少236个字节 .#temp表（减去118个nvarchar个字符） .要重现的完整脚本（在单用户模式下启动并使用sqlcmd模式的实例上运行最佳）
结果
对于较小的表（少于1000行），请使用临时变量，否则使用临时表 .
@wcm - 实际上要挑选表变量不仅仅是Ram - 它可以部分存储在磁盘上 .
临时表可以有索引，而表变量只能有主索引 . 如果速度是一个问题表变量可以更快，但显然如果有很多记录，或需要搜索聚集索引的临时表，然后临时表会更好 .
Good background article
表变量：但是表变量涉及我们通常创建普通表时的工作量 .
表变量：但表变量只能由当前用户使用 .
表变量：但是表变量将存储在物理内存中的某些数据，然后当大小增加时它将被移动到tempdb .
表变量：表变量不允许执行DDL操作 . 但是表变量允许我们仅创建聚簇索引 .
表变量：但表变量可以用于该程序 . （存储过程）
表变量：但我们不能对表变量这样做 .
表变量：但该函数允许我们使用表变量 . 但是使用表变量我们可以做到这一点 .
表变量：而表变量不会那样做 .
对于所有相信临时变量只在内存中的神话的人
首先，表变量不一定是内存驻留 . 在内存压力下，属于表变量的页面可以推送到tempdb .
阅读这里的文章：TempDB:: Table variable vs local temporary table
另一个主要区别是表变量没有列统计信息，而临时表则没有 . 这意味着查询优化器不知道表变量中有多少行（它猜测为1），如果表变量实际上有大量行，则可能导致生成高度非最优计划 .
报价取自; Professional SQL Server 2012 Internals and Troubleshooting
表变量不是在内存中创建的
There is a common misconception that table variables are in-memory structures and as such will perform quicker than temporary tables . 感谢称为sys的DMV . dm _ db _ session _ space _ usage，显示会话的tempdb使用情况， you can prove that’s not the case . 重新启动SQL Server以清除DMV后，运行以下脚本以确认您的session _ id为用户_对象返回0 _ alloc _ page _ count：
现在，您可以通过运行以下脚本来创建具有一列的临时表并使用一行填充临时表来检查临时表使用的空间量：
我的服务器上的结果表明该表在tempdb中分配了一个页面 . 现在运行相同的脚本，但这次使用表变量：
Which one to Use?
另一个区别：
一个table var只能从创建它的过程中的语句访问，而不能从该过程调用的其他过程或嵌套动态SQL（通过exec或sp_executesql）访问 .
另一方面，临时表的范围包括被调用过程中的代码和嵌套动态SQL .
如果必须可以从其他调用过程或动态SQL访问过程创建的表，则必须使用临时表 . 这在复杂情况下非常方便 .
还要考虑您经常可以用派生表替换它们，这些表也可能更快 . 但是，与所有性能调整一样，只有针对实际数据的实际测试才能告诉您特定查询的最佳方法 .
Temporary Table
临时表的行为类似于真实表，但在运行时创建 . 它的工作类似于真实表 . 我们几乎可以完成所有可能进入实际表格的操作 . 我们可以在临时表上使用ALTER，CREATE，DROP等DDL语句 .
创建后可以对临时表的结构进行任何更改 . 临时表存储在“tempdb”系统数据库的数据库中 .
临时表参与事务，日志记录或锁定 . 由于这个原因，它比表变量慢 .
Table Variable
它是可变的，但像 table 一样工作 . 它也被创建到Tempdb数据库而不是内存中 . 表变量仅在批处理或存储过程范围中可用 . 您无需删除表变量，它在批处理和存储过程执行过程完成时自动删除
表变量支持主键，创建时的标识 . 但它不支持非聚集索引 . 声明主键后，您无法修改它们 .
表变量不参与事务，日志记录或锁定 . 事务，日志记录和锁定不会影响表变量 .
阅读这篇文章了解更多 - http://goo.gl/GXtXqz