SQL Server 中的临时表和表变量有什么区别?
在 SQL Server 2005 中，我们可以通过以下两种方式之一创建临时表:
In SQL Server 2005, we can create temp tables one of two ways:
declare @tmp table (Col1 int, Col2 int);
或
create table #tmp (Col1 int, Col2 int);
这两者有什么区别?我已经阅读了关于@tmp 是否仍然使用 tempdb，或者一切都发生在内存中的相互矛盾的意见.
What are the differences between these two? I have read conflicting opinions on whether @tmp still uses tempdb, or if everything happens in memory.
在哪些情况下，一种优于另一种?
In which scenarios does one out-perform the other?
推荐答案
Temporary Tables (#tmp) 和 Table Variables (@tmp) 之间有一些区别，尽管使用 tempdb 不是其中之一，正如说明的那样在下面的 MSDN 链接中.
There are a few differences between Temporary Tables (#tmp) and Table Variables (@tmp), although using tempdb isn't one of them, as spelt out in the MSDN link below.
根据经验，对于中小型数据量和简单的使用场景，您应该使用表变量.(这是一个过于宽泛的指南，当然也有很多例外情况 - 请参阅下文和后续文章.)
As a rule of thumb, for small to medium volumes of data and simple usage scenarios you should use table variables. (This is an overly broad guideline with of course lots of exceptions - see below and following articles.)
在它们之间进行选择时需要考虑的几点:
Some points to consider when choosing between them:
临时表是真正的表，因此您可以执行创建索引等操作.如果您有大量数据，通过索引访问会更快，那么临时表是一个不错的选择.
Temporary Tables are real tables so you can do things like CREATE INDEXes, etc. If you have large amounts of data for which accessing by index will be faster then temporary tables are a good option.
表变量可以通过使用 PRIMARY KEY 或 UNIQUE 约束来拥有索引.(如果您想要非唯一索引，只需将主键列作为唯一约束中的最后一列.如果您没有唯一列，则可以使用标识列.)SQL 2014 也有非唯一索引.
Table variables can have indexes by using PRIMARY KEY or UNIQUE constraints. (If you want a non-unique index just include the primary key column as the last column in the unique constraint. If you don't have a unique column, you can use an identity column.) SQL 2014 has non-unique indexes too.
表变量不参与事务，并且 SELECT 隐含着 NOLOCK.事务行为可能非常有用，例如，如果您想在过程中途回滚，那么在该事务期间填充的表变量仍将被填充！
Table variables don't participate in transactions and SELECTs are implicitly with NOLOCK. The transaction behaviour can be very helpful, for instance if you want to ROLLBACK midway through a procedure then table variables populated during that transaction will still be populated!
临时表可能会导致存储过程被重新编译，可能经常发生.表变量不会.
Temp tables might result in stored procedures being recompiled, perhaps often. Table variables will not.
您可以使用 SELECT INTO 创建临时表，它可以更快地编写(适合临时查询)并且可以让您处理随时间变化的数据类型，因为您不需要定义你的临时表结构预先.
You can create a temp table using SELECT INTO, which can be quicker to write (good for ad-hoc querying) and may allow you to deal with changing datatypes over time, since you don't need to define your temp table structure upfront.
您可以从函数中传回表变量，使您能够更轻松地封装和重用逻辑(例如，创建一个函数以将字符串拆分为某个任意定界符上的值表).
You can pass table variables back from functions, enabling you to encapsulate and reuse logic much easier (eg make a function to split a string into a table of values on some arbitrary delimiter).
在用户定义的函数中使用表变量可以更广泛地使用这些函数(有关详细信息，请参阅 CREATE FUNCTION 文档).如果您正在编写函数，则应在临时表上使用表变量，除非另有迫切需要.
Using Table Variables within user-defined functions enables those functions to be used more widely (see CREATE FUNCTION documentation for details). If you're writing a function you should use table variables over temp tables unless there's a compelling need otherwise.
表变量和临时表都存储在 tempdb 中.但是表变量(自 2005 年以来)默认为当前数据库的排序规则，而临时表采用 tempdb 的默认排序规则(ref).这意味着如果使用临时表并且您的数据库排序规则与 tempdb 不同，您应该注意排序规则问题，如果您想将临时表中的数据与数据库中的数据进行比较，则会导致问题.
Both table variables and temp tables are stored in tempdb. But table variables (since 2005) default to the collation of the current database versus temp tables which take the default collation of tempdb (ref). This means you should be aware of collation issues if using temp tables and your db collation is different to tempdb's, causing problems if you want to compare data in the temp table with data in your database.
全局临时表 (##tmp) 是另一种可供所有会话和用户使用的临时表.
Global Temp Tables (##tmp) are another type of temp table available to all sessions and users.
进一步阅读:
Martin Smith 在 dba.stackexchange.com 上的精彩回答
关于两者区别的 MSDN 常见问题解答:https://support.microsoft.com/en-gb/kb/305977
MSDN FAQ on difference between the two: https://support.microsoft.com/en-gb/kb/305977
MDSN 博客文章:https://docs.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 上的 Paul White
Unexpected behaviors and performance implications of temp tables and temp variables: Paul White on SQLblog.com
相关文章