临时表(##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.

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

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

好的背景文章

另一个区别:

表var只能从创建它的过程中的语句中访问，而不能从该过程调用的其他过程或嵌套的动态SQL(通过exec或sp_executesql)访问。

另一方面，临时表的作用域包括调用过程和嵌套动态SQL中的代码。

如果由过程创建的表必须可以从其他被调用的过程或动态SQL中访问，则必须使用临时表。这在复杂的情况下非常方便。

引用自;专业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!

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

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

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

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

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