在SQL Server中，何时应该使用表变量而不是临时表？

19 浏览
0 Comments

在SQL Server中，何时应该使用表变量而不是临时表？

我正在学习表变量的更多细节。它说临时表始终存储在磁盘上，而表变量存储在内存中，也就是说，表变量的性能比临时表更好，因为表变量使用的IO操作较少。\n但有时，如果表变量中的记录太多，无法容纳在内存中，那么表变量会像临时表一样被存储在磁盘上。\n但是我不知道什么是“太多的记录”。是10万条记录？还是100万条记录？我如何知道我正在使用的表变量是存储在内存中还是存储在磁盘上？在SQL Server 2005中是否有任何函数或工具来测量表变量的规模或让我知道表变量何时从内存转移到磁盘上？

0

3 答案

0 Comments

在SQL Server中，当需要在查询中保存临时数据时，可以使用表变量（table variable）或临时表（temporary table）。然而，根据微软的文档，选择何时使用表变量或临时表是有考虑的。在某些情况下，表变量可能不适合处理较大数量的数据（大于100行），而临时表可能是更好的选择。

根据微软的文档，表变量没有分布统计信息，这意味着它们不会触发重新编译。因此，在许多情况下，查询优化器会基于表变量没有行的假设来构建查询计划。这就是为什么在预期有较大数量的行（大于100）时，使用表变量需要谨慎。在这种情况下，临时表可能是更好的解决方案。

那么，如何确定何时使用表变量或临时表呢？一种方法是根据数据量来决定。如果预计数据量较小，表变量可能是一个简单和有效的选择。但是，如果预计数据量较大，则应考虑使用临时表。

以下是一个示例代码，展示了如何使用表变量和临时表：

-- 使用表变量

DECLARE @TableVariable TABLE (

ID INT,

Name VARCHAR(50)

)

INSERT INTO @TableVariable

SELECT ID, Name

FROM SomeTable

WHERE SomeCondition

-- 使用临时表

CREATE TABLE #TempTable (

ID INT,

Name VARCHAR(50)

)

INSERT INTO #TempTable

SELECT ID, Name

FROM SomeTable

WHERE SomeCondition

根据微软的建议，如果预计数据量较大（大于100），则应使用临时表而不是表变量。这是因为临时表可以拥有分布统计信息，并且查询优化器可以更好地为其构建查询计划。

总之，在决定何时使用表变量或临时表时，请考虑数据量的大小。对于较小的数据量，表变量可能是一个简单和有效的选择。但是，对于较大的数据量，应该使用临时表以获得更好的性能。

0
0 Comments

在SQL Server中，我们可以选择使用表变量（table variable）或临时表（temporary table）来存储数据。那么什么时候应该使用表变量，什么时候应该使用临时表呢？

根据经验，如果数据量很小（千字节级别），那么使用表变量是合适的选择。但如果数据量很大，那么使用临时表会更好。我们可以这样理解：如果你认为使用索引、自动统计信息或者任何SQL优化器的功能会对你有好处，那么你的数据集很可能太大了，不能使用表变量。

举个例子，假设我们需要对大约20行数据进行格式化并一起修改，然后再将其用于更新/插入永久表。这种情况下，表变量是完美的选择。但如果我们需要一次性填充数千行数据，那么临时表的性能要比表变量好得多。

这与CTE（公共表达式）的情况类似，对于非常小的数据集，CTE的性能可能和优化器生成的结果一样好甚至更好，但如果数据集很大，CTE的性能就会大大下降。

总结一下，对于小数据集，使用表变量是可以的，而对于大数据集，应该使用临时表。我有一个包含数千行数据的查询。通过将表变量切换为临时表，查询时间从40秒降低到只有5秒，其他条件不变。

参考链接：http://www.developerfusion.com/article/84397/table-variables-v-temporary-tables-in-sql-server/

0
0 Comments

当应该在SQL Server中使用表变量还是临时表？

您的问题显示出您已经陷入了与表变量和临时表相关的一些常见误解。

我在DBA网站上写了一个相当详细的答案，介绍了这两种对象类型之间的区别。这也回答了您关于磁盘与内存的问题（我没有看到两者之间有任何显著的行为差异）。

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

当您有选择余地时，以下是一些建议（尽管最可靠的方法是使用您特定工作负载进行测试）。

  1. 如果需要在表变量上创建无法创建的索引，则当然需要一个#temporary表。但是，这方面的细节取决于版本。对于SQL Server 2012及以下版本，只能通过UNIQUE或PRIMARY KEY约束隐式创建索引。SQL Server 2014引入了内联索引语法，用于CREATE INDEX中的一些可用选项的子集。此后，已扩展允许使用过滤索引条件。但是，仍然无法在表变量上创建带有INCLUDE列或列存储索引的索引。
  2. 如果您将反复添加和删除大量行，则使用#temporary表。它支持TRUNCATE（对于大型表而言，比DELETE更高效），并且在TRUNCATE后的后续插入操作可能比在DELETE后的插入操作性能更好（如此处所示）。
  3. 如果您将删除或更新大量行，则临时表可能比表变量表现更好，如果它能够使用行集共享（请参阅下面的“行集共享的影响”示例）。
  4. 如果使用表的查询的最佳计划因数据而异，则使用#temporary表。它支持创建统计信息，允许根据数据动态重新编译计划（尽管对于存储过程中的缓存临时表，需要单独了解重新编译行为）。
  5. 如果使用表的查询的最佳计划很可能永远不会改变，则可以考虑使用表变量，以跳过统计信息创建和重新编译的开销（可能需要提示来修复所需的计划）。
  6. 如果插入到表中的数据来源于可能昂贵的SELECT语句，则考虑使用表变量将阻止此使用并行计划的可能性。
  7. 如果需要在外部用户事务回滚时保留表中的数据，则使用表变量。这可能的用例是记录长SQL批处理中不同步骤的进度。
  8. 在使用用户事务中的#temp表时，锁定的持续时间可能比表变量长（取决于锁定类型和隔离级别），并且还可能阻止tempdb事务日志的截断，直到用户事务结束。因此，这可能会支持使用表变量。
  9. 在存储过程中，表变量和临时表都可以被缓存。缓存表变量的元数据维护比#temporary表少。Bob Ward在他的tempdb演示中指出，在高并发条件下，这可能会导致系统表上的额外争用。此外，在处理小量数据时，这可能会对性能产生可测量的影响。

行集共享的影响

DECLARE  TABLE(id INT PRIMARY KEY, Flag BIT);
CREATE TABLE #T (id INT PRIMARY KEY, Flag BIT);
INSERT INTO  
output inserted.* into #T
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY @), 0
FROM master..spt_values v1, master..spt_values v2
SET STATISTICS TIME ON
/*CPU time = 7016 ms,  elapsed time = 7860 ms.*/
UPDATE  SET Flag=1;
/*CPU time = 6234 ms,  elapsed time = 7236 ms.*/
DELETE FROM 
/* 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

嗨，Martin Smith先生。在我的情况下，我只想存储一组Id值，以便在存储过程内部的其他查询中使用它们。那么您建议我使用什么？

- 在id上使用带有主键的表变量，并使用OPTION (RECOMPILE)可能是可以的-但是请测试两种方法。

元数据争用对临时表和表变量是否相同？

. 通常表变量较少。如果在用户事务内部，则可以更早地释放锁定。另请参阅Bob Ward的链接。

0