在SQL Server 2005中，我们可以使用以下两种方式之一创建临时表：

declare @tmp table (Col1 int, Col2 int);

或者

create table #tmp (Col1 int, Col2 int);

两者之间有什么区别？对于@tmp是否仍然使用tempdb，或者是否所有事情都在内存中，我已经读到了相互矛盾的意见。

在哪种情况下，一种表现优于另一种？

解决方案

回答

@wcm实际上可以选择表变量不是Ram，它只能部分存储在磁盘上。

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

好背景文章

回答

对于所有相信temp变量仅在内存中的神话的人

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

在这里阅读文章：TempDB ::表变量与本地临时表

回答

In which scenarios does one out-perform the other?

对于较小的表(少于1000行)，请使用temp变量，否则请使用temp表。

回答

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

根据经验，对于中小型数据量和简单使用场景，应使用表变量。 (这是一个过于宽泛的准则，当然还有很多例外，请参见下文和后续文章。)

在它们之间进行选择时应考虑以下几点：

• 临时表是真实表，因此我们可以执行诸如CREATE INDEXes等操作。如果我们有大量数据，通过索引对其进行访问将更快，那么临时表是一个不错的选择。
• 通过使用PRIMARY KEY或者UNIQUE约束，表变量可以具有索引。 (如果要非唯一索引，只需将主键列作为唯一约束中的最后一列。如果没有唯一列，则可以使用标识列。)SQL 2014也具有非唯一索引。
• 表变量不参与事务，而SELECT与NOLOCK隐式关联。事务行为可能非常有帮助，例如，如果我们想在过程中途回滚，那么在该事务期间填充的表变量仍将被填充！
• 临时表可能导致存储过程可能经常被重新编译。表变量不会。
• 我们可以使用SELECT INTO创建临时表，这样可以更快地编写(适合临时查询)，并且可以让我们处理随时间变化的数据类型，因为我们无需预先定义临时表结构。
• 我们可以将表变量从函数传递回来，从而使封装和重用逻辑变得更加容易(例如，使函数将字符串拆分为任意定界符上的值表)。
• 在用户定义的函数中使用表变量可以使这些函数得到更广泛的使用(有关详细信息，请参见CREATE FUNCTION文档)。如果要编写函数，则除非临时有其他必要，否则应在临时表上使用表变量。
• 表变量和临时表都存储在tempdb中。但是表变量(自2005年起)默认为当前数据库的排序规则，而临时表则采用采用tempdb(参考)的默认排序规则的临时表。这意味着如果使用临时表并且数据库排序规则与tempdb的排序规则不同，则应该注意排序规则问题，如果要将临时表中的数据与数据库中的数据进行比较，则会引起问题。
• 全局临时表(## tmp)是可用于所有会话和用户的另一种临时表。

一些进一步的阅读：

• 马丁·史密斯(Martin Smith)在dba.stackexchange.com上的出色回答
• 关于两者之间区别的MSDN常见问题解答：https://support.microsoft.com/zh-cn/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#
• 临时表和临时变量的异常行为和性能影响：SQLblog.com上的Paul White

回答

还请考虑一下，我们经常可以用派生表替换这两个表，而派生表也可能更快。但是，与所有性能调整一样，只有针对实际数据进行的实际测试才能告诉我们针对特定查询的最佳方法。

回答

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