什么时候应该在sql服务器中使用表变量与临时表？

Question

我正在学习表变量的更多细节。它说临时表总是在磁盘上，表变量在内存中，也就是说，表变量的性能比临时表好，因为表变量比临时表使用更少的输入输出操作。

但有时，如果一个表变量中有太多无法包含在内存中的记录，该表变量将像临时表一样放在磁盘上。

但我不知道“记录太多”是什么。100000条记录？还是1000000条记录？如何知道我使用的表变量是在内存中还是在磁盘上？SQL Server 2005中是否有任何函数或工具可以测量表变量的规模，或者让我知道表变量何时从内存中放入磁盘？

Answer 1

微软在这里说

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

Answer 2

如果数据量非常小（数千字节），请使用表变量

对大量数据使用临时表

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

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

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

这与CTE因相似大小的原因而受到关注的方式没有什么不同-如果CTE中的数据非常小，我发现CTE的性能与优化器的性能一样好或更好，但如果它非常大，则会对您造成很大的伤害。

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

Answer 3

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

我在DBA站点上写了一个相当广泛的答案，讨论了这两种对象类型之间的差异。这也解决了您关于磁盘与内存的问题（我看不出两者在行为上有任何显著差异）。

关于标题中关于何时使用表变量和本地临时表的问题，您并不总是有选择的余地。例如，在函数中，只能使用表变量，如果需要在子作用域中写入表，则只能使用临时表（表值参数允许只读访问）。

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

>

如果要从表中重复添加和删除大量行，请使用临时表。支持截短（对于大型表，这比删除更有效）以及在截短之后的后续插入可以比删除之后的插入具有更好的性能，如图所示。

行集共享的影响

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