SQL Server 中的临时表和表变量有什么区别？

在SQL Server 2005中，我们可以通过两种不同的方式创建类似的表。

我们可以使用表变量：

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

或者我们可以使用临时表：

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

这两者有什么区别？我读过关于是否@tmp仍然使用 tempdb，或者如果一切都发生在内存中。

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

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

根据经验，对于中小型数据量和简单的使用场景，您应该使用表变量。 （这是一个过于宽泛的指南，当然也有很多例外 - 请参阅下文和后续文章。）

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

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

• 表变量可以通过使用 PRIMARY KEY 或 UNIQUE 约束来拥有索引。 （如果您想要非唯一索引，只需将主键列作为唯一约束中的最后一列。如果您没有唯一列，则可以使用标识列。）SQL 2014 也有非唯一索引.

• 表变量不参与事务并且SELECTs 隐式地与NOLOCK。事务行为可能非常有帮助，例如，如果您想在过程中进行回滚，那么在该事务期间填充的表变量仍然会被填充！

• 临时表可能会导致存储过程被重新编译（也许经常）。表变量不会。

• 您可以使用 SELECT INTO 创建临时表，这样可以更快地编写（有利于即席查询），并且可以允许您处理随时间变化的数据类型，因为您不需要预先定义临时表结构。

• 您可以从函数传回表变量，从而使您能够更轻松地封装和重用逻辑（例如，创建一个函数将字符串拆分为某个任意分隔符上的值表）。

• 在用户定义的函数中使用表变量可以使这些函数得到更广泛的使用（有关详细信息，请参阅 CREATE FUNCTION 文档）。如果您正在编写函数，则应该使用表变量而不是临时表，除非有迫切需要。

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

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

一些进一步阅读：

• 马丁·史密斯的精彩回答在 dba.stackexchange.com 上

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

• MDSN 博客文章：https://learn.microsoft.com/archive/blogs/sqlserverstorageengine/tempdb-table-variable-vs-local-temporary-table

• 文章：https://searchsqlserver.techtarget.com/tip/Temporary-tables-in-SQL-Server-vs-table-variables

• 临时表和临时变量的意外行为和性能影响：保罗·怀特在 SQLblog.com 上