什么时候应该在sql server中使用表变量vs临时表？

6 回答

• 316

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

  我写了quite an extensive answer on the DBA site看两种对象类型之间的差异 . 这也解决了你关于光盘与内存的问题（我没有看到两者之间的行为有任何显着差异） .

  关于 Headers 中的问题，关于何时使用表变量与本地临时表，您并不总是有选择 . 例如，在函数中，只能使用表变量，如果需要在子作用域中写入表，那么只有 #temp 表可以执行（表值参数允许readonly access） .

  您可以在哪里选择一些建议如下（尽管最可靠的方法是简单地测试您的特定工作量） .

  • 如果需要无法通过 UNIQUE 或 PRIMARY KEY 约束隐式创建的索引，则需要 #temporary 表，因为无法在表变量上创建这些索引 . （此类索引的示例是非唯一索引，筛选索引或具有 INCLUDE d列的索引） . 注意：SQL Server 2014将允许为表变量内联声明非唯一索引 .

  • 如果要从表中重复添加和删除大量行，请使用 #temporary 表 . 这支持 TRUNCATE （对于大型表来说比 DELETE 更有效）并且 TRUNCATE 之后的后续插入可以比后面的 DELETE as illustrated here具有更好的性能 .

  • 如果要删除或更新大量行，那么临时表可能比表变量好得多 - 如果它能够使用行集共享（参见下面的"Effects of rowset sharing"示例） .

  • 如果使用该表的最佳计划将根据数据而变化，则使用 #temporary 表 . 这支持创建统计信息，允许根据数据动态重新编译计划（尽管存储过程中的缓存临时表需要单独理解） .

  • 如果使用该表的查询的最佳计划不太可能发生变化，那么您可以考虑使用表变量来跳过统计信息创建和重新编译的开销（可能需要提示来修复所需的计划） .

  • 如果插入表中的数据源来自可能很昂贵的 SELECT 语句，那么考虑使用表变量将阻止使用并行计划的可能性 .

  • 如果您需要表中的数据以回滚外部用户事务，那么请使用表变量 . 可能的用例可能是在长SQL批处理中记录不同步骤的进度 .

  • 在用户事务中使用 #temp 表时，锁可以保持比表变量更长的时间（可能直到事务结束与语句结束取决于锁的类型和隔离级别），并且还可以防止截断 tempdb 事务记录直到用户事务结束 . 所以这可能有利于使用表变量 .

  • 在存储的例程中，可以缓存表变量和临时表 . 缓存表变量的元数据维护小于 #temporary 表的元数据维护 . Bob Ward在他的_1684868中指出，这可能会导致在高并发条件下对系统表进行额外争用 . 此外，在处理少量数据时，这可能会产生a measurable difference to performance .

  Effects of rowset sharing

  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
  

  回复于 2024-04-19T04:14:11+08:00
• 4

  如果是非常少量的数据，请使用 table variable 字节）

  使用 temporary table 获取大量数据

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

  在我的示例中，我只想将大约20行放入格式中并将它们作为一组修改，然后再使用它们来更新/插入永久表 . 所以表变量是完美的 .

  但是我也在运行SQL来一次回填几千行，我可以肯定地说临时表的表现比表变量好得多 .

  这与CTE如何关注类似尺寸的原因没有什么不同 - 如果CTE中的数据非常小，我发现CTE的性能与优化器的性能一样好或更好，但是如果它相当大，那么它伤害了你 .

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

  回复于 2024-04-19T04:14:11+08:00
• 60

  微软says here

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

  回复于 2024-04-19T04:14:11+08:00
• 3

  我完全同意Abacus（对不起 - 没有足够的评论点） .

  此外，请记住，它不一定取决于您拥有多少条记录，而是记录的大小 .

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

  最后，也许你're querying/storing more data than you need? Here'是一个很好的读SQL optimization strategies . 限制你没有全部使用的数据量（一些SQL程序员确实很懒，只是选择所有内容，即使他们只使用一小部分） . 不要忘记SQL查询分析器也可能成为你最好的朋友 .

  回复于 2024-04-19T04:14:11+08:00
• 14

  Variable table 仅适用于当前会话，例如，如果您需要在当前会话中使用 EXEC 另一个存储过程，则必须将该表作为 Table Valued Parameter 传递，当然这会影响性能， temporary tables 只能执行此操作传递临时表名

  To test a Temporary table:

  • 开放式管理工作室查询编辑器

  • 创建临时表

  • 打开另一个查询编辑器窗口

  • 从此表中选择"Available"

  To test a Variable table:

  • 开放式管理工作室查询编辑器

  • 创建变量表

  • 打开另一个查询编辑器窗口

  • 从此表中选择"Not Available"

  我遇到的其他事情是：如果您的架构没有 GRANT 权限来创建表，那么使用变量表 .

  回复于 2024-04-19T04:14:11+08:00
• 34

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

  当我用@tb加入它们时，返回结果的时间要长得多，与#tm不同，返回几乎是瞬间完成的 .

  我用10,000行连接进行测试，并与其他5个表连接

  回复于 2024-04-19T04:14:11+08:00