什么时候我应该使用一个表变量vs临时表在sql server?

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

但是有时候，如果一个表变量中有太多记录不能包含在内存中，那么这个表变量就会像临时表一样被放到磁盘上。

但我不知道“太多记录”是什么意思。100000条记录吗?还是100万条记录?我如何知道我正在使用的表变量是在内存中还是在磁盘上?在SQL Server 2005中是否有任何函数或工具来测量表变量的规模，或者让我知道表变量何时从内存中放到磁盘上?

234861 次浏览
小开
最佳答案

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

我已经编写了DBA网站上有相当广泛的答案来查看这两种对象类型之间的差异。这也解决了您关于磁盘和内存的问题(我没有看到两者之间的行为有任何显著差异)。

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

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

  1. 如果你需要一个不能在表变量上创建的索引，那么你当然需要一个#temporary表。不过，这方面的细节取决于版本。对于SQL Server 2012及以下版本，唯一可以在表变量上创建的索引是那些通过UNIQUE或PRIMARY KEY约束隐式创建的索引。SQL Server 2014为CREATE INDEX中可用选项的一个子集引入了内联索引语法。此后，这一功能得到了扩展，允许过滤索引条件。但是，仍然不能在表变量上创建带有INCLUDE-d列或columnstore索引的索引。

  2. 如果你要重复地从表中添加和删除大量的行，那么使用#temporary表。它支持TRUNCATE(对于大型表，它比DELETE更有效)，并且在TRUNCATE之后的后续插入比在DELETE 后面的插入具有更好的性能，如图所示。

  3. 如果要删除或更新大量的行，那么临时表的性能可能比表变量好得多——如果它能够使用行集共享(参见下面的“行集共享的影响”以获得示例)。
  4. 如果使用表的最佳计划取决于数据，则使用#temporary表。它支持统计数据的创建，允许计划根据数据动态重新编译(尽管对于存储过程中的缓存临时表重新编译行为需要单独理解)。
  5. 如果使用表的查询的最佳计划不太可能改变，那么您可以考虑使用表变量来跳过统计数据创建和重新编译的开销(可能需要提示来修复您想要的计划)。
  6. 如果插入到表中的数据的源来自可能昂贵的SELECT语句，则考虑使用表变量将阻止使用并行计划的可能性。
  7. 如果需要表中的数据在外部用户事务回滚后仍然存在，则使用表变量。一个可能的用例可能是记录长SQL批处理中不同步骤的进度。
  8. 当在用户事务中使用#temp表时，事务锁可以比表变量保持更长的时间(可能直到事务结束vs语句结束，这取决于锁的类型和隔离级别)，并且它还可以防止截断tempdb事务日志，直到用户事务结束。所以这可能有利于表变量的使用。
  9. 在存储的例程中，表变量和临时表都可以缓存。缓存表变量的元数据维护比#temporary表的元数据维护少。Bob Ward在他的tempdb演讲中指出，在高并发条件下，这会导致系统表上的额外争用。此外，当处理少量数据时，这可以使可衡量的性能差异.;

行集共享的影响

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当前存储过程中的另一个存储过程，你必须将表作为Table Valued Parameter传递，当然这将影响性能，而使用临时表，你可以只传递临时表名来实现这一点

测试一个临时表:

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

测试一个变量表:

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

如果你的模式没有GRANT特权来创建表，那么就使用变量表。

小开

在声明为declare @tb的表中写入数据，并与其他表连接后，我意识到与临时表tempdb .. # tb相比，响应时间要高得多。

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

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


提交答案