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

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

好的背景文章

对于所有相信临时变量只存在于内存中的人

首先，表变量不一定存在于内存中。在内存压力下，属于表变量的页可以被推到tempdb。

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

在哪些情况下，一个会优于另一个?

对于较小的表(小于1000行)，使用临时变量，否则使用临时表。

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.

引用自;专业SQL Server 2012内部和故障处理

Statistics The major difference between temp tables and table variables is that statistics are not created on table variables. This has two major consequences, the fi rst of which is that the Query Optimizer uses a fi xed estimation for the number of rows in a table variable irrespective of the data it contains. Moreover, adding or removing data doesn’t change the estimation. Indexes You can’t create indexes on table variables although you can create constraints. This means that by creating primary keys or unique constraints, you can have indexes (as these are created to support constraints) on table variables. Even if you have constraints, and therefore indexes that will have statistics, the indexes will not be used when the query is compiled because they won’t exist at compile time, nor will they cause recompilations. Schema Modifications Schema modifications are possible on temporary tables but not on table variables. Although schema modifi cations are possible on temporary tables, avoid using them because they cause recompilations of statements that use the tables.

表变量不是在内存中创建的

有一种常见的误解，认为表变量是内存中的结构 这样会比临时表执行得更快。多亏了车管所 叫做sys。“Dm _ db _ session _ space _ usage”，显示tempdb的使用情况 塞申斯，你可以证明事实并非如此。重新启动SQL Server后清除 DMV，运行以下脚本确认您的session _ id返回0 User _ objects _ 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 ;

服务器上的结果表明该表在tempdb中分配了一个页面。 现在运行相同的脚本，但是使用一个表变量 这一次:

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 ;

用哪一个?

Whether or not you use temporary tables or table variables should be decided by thorough testing, but it’s best to lean towards temporary tables as the default because there are far fewer things that can go wrong. I’ve seen customers develop code using table variables because they were dealing with a small amount of rows, and it was quicker than a temporary table, but a few years later there were hundreds of thousands of rows in the table variable and performance was terrible, so try and allow for some capacity planning when you make your decision!

还要考虑到，您通常可以用派生表来替换这两个表，这也可能更快。但是，与所有性能调优一样，只有针对实际数据的实际测试才能告诉您特定查询的最佳方法。