临时表(##temp/#temp)和表变量(@table)之间的区别如下:

Table variable (@table) is created in the memory. Whereas, a Temporary table (##temp/#temp) is created in the tempdb database. However, if there is a memory pressure the pages belonging to a table variable may be pushed to tempdb. Table variables cannot be involved in transactions, logging or locking. This makes @table faster then #temp. So table variable is faster then temporary table. Temporary table allows Schema modifications unlike Table variables. Temporary tables are visible in the created routine and also in the child routines. Whereas, Table variables are only visible in the created routine. Temporary tables are allowed CREATE INDEXes whereas, Table variables aren’t allowed CREATE INDEX instead they can have index by using Primary Key or Unique Constraint.

临时表(##temp/#temp)和表变量(@table)之间的区别如下:

Table variable (@table) is created in the memory. Whereas, a Temporary table (##temp/#temp) is created in the tempdb database. However, if there is a memory pressure the pages belonging to a table variable may be pushed to tempdb. Table variables cannot be involved in transactions, logging or locking. This makes @table faster then #temp. So table variable is faster then temporary table. Temporary table allows Schema modifications unlike Table variables. Temporary tables are visible in the created routine and also in the child routines. Whereas, Table variables are only visible in the created routine. Temporary tables are allowed CREATE INDEXes whereas, Table variables aren’t allowed CREATE INDEX instead they can have index by using Primary Key or Unique Constraint.

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

根据经验，对于中小数据量和简单的使用场景，应该使用表变量。(这是一个过于宽泛的指导方针，当然有很多例外-请参阅下面和下面的文章。)

在两者之间进行选择时需要考虑以下几点:

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. 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. 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. 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). 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. 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. 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 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 临时表和临时变量的意外行为和性能影响:Paul White在SQLblog.com上的文章

Temp table: A Temp table is easy to create and back up data. Table variable: But the table variable involves the effort when we usually create the normal tables. Temp table: Temp table result can be used by multiple users. Table variable: But the table variable can be used by the current user only.  Temp table: Temp table will be stored in the tempdb. It will make network traffic. When we have large data in the temp table then it has to work across the database. A Performance issue will exist. Table variable: But a table variable will store in the physical memory for some of the data, then later when the size increases it will be moved to the tempdb. Temp table: Temp table can do all the DDL operations. It allows creating the indexes, dropping, altering, etc.., Table variable: Whereas table variable won't allow doing the DDL operations. But the table variable allows us to create the clustered index only. Temp table: Temp table can be used for the current session or global. So that a multiple user session can utilize the results in the table. Table variable: But the table variable can be used up to that program. (Stored procedure) Temp table: Temp variable cannot use the transactions. When we do the DML operations with the temp table then it can be rollback or commit the transactions. Table variable: But we cannot do it for table variable. Temp table: Functions cannot use the temp variable. More over we cannot do the DML operation in the functions . Table variable: But the function allows us to use the table variable. But using the table variable we can do that. Temp table: The stored procedure will do the recompilation (can't use same execution plan) when we use the temp variable for every sub sequent calls. Table variable: Whereas the table variable won't do like that.

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

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

好的背景文章

令我惊讶的是，没有人提到这两者之间的关键区别是临时表支持并行插入，而表变量不支持。您应该能够从执行计划中看出不同之处。下面是9频道和MSDN doc上的SQL workshop的视频。

这也解释了为什么应该对较小的表使用表变量，否则应该使用临时表，正如SQLMenace前面回答的那样。