您的问题表明您已经屈服于围绕表变量和临时表的一些常见误解。

我在DBA站点上写了一篇详细的回答，讨论了这两种对象类型之间的差异。这也解决了您关于磁盘和内存的问题(我没有看到两者之间的行为有任何显著差异)。

关于标题中的问题，什么时候使用表变量和本地临时表，你并不总是有选择。例如，在函数中，只能使用表变量，如果需要在子作用域中写入表，则只能使用#temp表 (表值参数允许只读访问)。

下面是一些建议(尽管最可靠的方法是在特定的工作负载下简单地测试两者)。

If you need an index that cannot be created on a table variable then you will of course need a #temporary table. The details of this are version dependant however. For SQL Server 2012 and below the only indexes that could be created on table variables were those implicitly created through a UNIQUE or PRIMARY KEY constraint. SQL Server 2014 introduced inline index syntax for a subset of the options available in CREATE INDEX. This has been extended since to allow filtered index conditions. Indexes with INCLUDE-d columns or columnstore indexes are still not possible to create on table variables however. If you will be repeatedly adding and deleting large numbers of rows from the table then use a #temporary table. That supports TRUNCATE (which is more efficient than DELETE for large tables) and additionally subsequent inserts following a TRUNCATE can have better performance than those following a DELETE as illustrated here. If you will be deleting or updating a large number of rows then the temp table may well perform much better than a table variable - if it is able to use rowset sharing (see "Effects of rowset sharing" below for an example). If the optimal plan using the table will vary dependent on data then use a #temporary table. That supports creation of statistics which allows the plan to be dynamically recompiled according to the data (though for cached temporary tables in stored procedures the recompilation behaviour needs to be understood separately). If the optimal plan for the query using the table is unlikely to ever change then you may consider a table variable to skip the overhead of statistics creation and recompiles (would possibly require hints to fix the plan you want). If the source for the data inserted to the table is from a potentially expensive SELECT statement then consider that using a table variable will block the possibility of this using a parallel plan. If you need the data in the table to survive a rollback of an outer user transaction then use a table variable. A possible use case for this might be logging the progress of different steps in a long SQL batch. When using a #temp table within a user transaction locks can be held longer than for table variables (potentially until the end of transaction vs end of statement dependent on the type of lock and isolation level) and also it can prevent truncation of the tempdb transaction log until the user transaction ends. So this might favour the use of table variables. Within stored routines, both table variables and temporary tables can be cached. The metadata maintenance for cached table variables is less than that for #temporary tables. Bob Ward points out in his tempdb presentation that this can cause additional contention on system tables under conditions of high concurrency. Additionally, when dealing with small quantities of data this can make a measurable difference to performance.

行集共享的影响

DECLARE @T TABLE(id INT PRIMARY KEY, Flag BIT);

CREATE TABLE #T (id INT PRIMARY KEY, Flag BIT);

INSERT INTO @T 
output inserted.* into #T
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY @@SPID), 0
FROM master..spt_values v1, master..spt_values v2

SET STATISTICS TIME ON

/*CPU time = 7016 ms,  elapsed time = 7860 ms.*/
UPDATE @T SET Flag=1;

/*CPU time = 6234 ms,  elapsed time = 7236 ms.*/
DELETE FROM @T

/* CPU time = 828 ms,  elapsed time = 1120 ms.*/
UPDATE #T SET Flag=1;

/*CPU time = 672 ms,  elapsed time = 980 ms.*/
DELETE FROM #T

DROP TABLE #T

如果数据量很小(数千字节)，则使用表变量

对大量数据使用临时表

另一种考虑方法是:如果您认为可以从索引、自动统计数据或任何SQL优化器中受益，那么您的数据集对于表变量来说可能太大了。

在我的示例中，我只想将大约20行放入一种格式，并将它们作为一个组进行修改，然后使用它们来UPDATE / INSERT一个永久表。所以表变量是完美的。

但是我也运行SQL一次回填数千行，我可以肯定地说，临时表的性能比表变量好得多。

这与CTE的大小相似——如果CTE中的数据非常小，我发现CTE的性能与优化器提供的数据一样好，甚至更好，但如果它相当大，那么它会对您造成严重伤害。

我的理解主要基于http://www.developerfusion.com/article/84397/table-variables-v-temporary-tables-in-sql-server/，它有更多的细节。

微软说

表变量没有分布统计信息，它们不会触发重新编译。因此，在许多情况下，优化器将在假定表变量没有行的基础上构建查询计划。出于这个原因，如果需要更大的行数(大于100)，则应该谨慎使用表变量。在这种情况下，临时表可能是更好的解决方案。

我完全同意Abacus的观点(抱歉，我没有足够的分数来评论)。

另外，请记住，这并不一定取决于您有多少记录，而是取决于您的记录的大小。

例如，您是否考虑过每个记录有50个列的1,000条记录与每个记录只有5个列的100,000条记录之间的性能差异?

最后，可能您查询/存储的数据超过了您的需要?这里有一篇关于SQL优化策略的好文章。限制您提取的数据量，特别是如果您没有使用所有数据(一些SQL程序员确实很懒，即使他们只使用很小的子集，也会选择所有数据)。不要忘记SQL查询分析器也可能成为您最好的朋友。

变量表仅对当前会话可用，例如，如果您需要EXEC当前存储过程中的另一个存储过程，则必须将表作为表值参数传递，当然这将影响性能，对于临时表，您可以仅通过传递临时表名来实现这一点

测试一个临时表:

打开管理工作室查询编辑器 创建临时表 打开另一个查询编辑器窗口 从这个表中选择“Available”

测试一个变量表:

打开管理工作室查询编辑器 创建一个变量表 打开另一个查询编辑器窗口 从这个表中选择“不可用”

我的另一个经验是:如果你的模式没有GRANT特权来创建表，那么就使用变量表。

写数据在表声明声明@tb和加入其他表后，我意识到响应时间相比临时表tempdb ..# TB要高得多。

当我用@tb连接它们时，返回结果的时间要长得多，不像#tm，返回几乎是瞬时的。

我使用10,000行连接和与其他5个表连接进行了测试