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

Question

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

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

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

Answer 1

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

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

关于标题中的问题，即何时使用表变量与本地临时表，您并不总是有选择。例如，在函数中，只能使用表变量，如果您需要在子作用域中写入表，则只有 #temp表会做
(表值参数允许 readonly access )。

您可以选择以下一些建议(尽管最可靠的方法是使用您的特定工作负载简单地测试两者)。

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

如果您将重复从表中添加和删除大量行，请使用 #temporary table 。支持TRUNCATE (对于大型表，这比 DELETE 更有效)以及在 TRUNCATE 之后的附加后续插入可以比遵循 DELETE 的那些具有更好的性能as illustrated here .

如果您要删除或更新大量行，那么临时表的性能可能比表变量好得多 - 如果它能够使用行集共享(请参阅下面的“行集共享的影响”示例)。

如果使用该表的最佳计划会因数据而异，则使用 #temporary table 。这支持创建统计信息，允许根据数据动态重新编译计划(尽管对于存储过程中的缓存临时表 the recompilation behaviour 需要单独理解)。

如果使用该表的查询的最佳计划不太可能改变，那么您可以考虑使用表变量来跳过统计信息创建和重新编译的开销(可能需要提示来修复您想要的计划)。

如果插入到表中的数据的源来自潜在的昂贵 SELECT语句然后考虑使用表变量将阻止使用并行计划的可能性。

如果您需要表中的数据在外部用户事务回滚后继续存在，请使用表变量。一个可能的用例可能是记录长 SQL 批处理中不同步骤的进度。

使用 #temp 时用户事务锁中的表可以比表变量持有更长的时间(可能直到事务结束与语句结束，这取决于锁的类型和隔离级别)，并且它还可以防止截断 tempdb事务日志，直到用户事务结束。所以这可能有利于使用表变量。

在存储例程中，可以缓存表变量和临时表。缓存表变量的元数据维护少于#temporary表。 Bob Ward 在他的 tempdb presentation 中指出在高并发的情况下，这可能会导致系统表上的额外争用。此外，在处理少量数据时，这会使 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