我正在表变量中了解更多详细信息。它说临时表总是在磁盘上，而表变量在内存中，也就是说，表变量的性能比临时表更好，因为表变量比临时表使用更少的IO操作。
但有时，如果表变量中的记录太多而无法容纳在内存中，则该表变量将像临时表一样放在磁盘上。
但我不知道“记录太多”是什么。 100,000 条记录？或 1000,000 条记录？我如何知道我正在使用的表变量是在内存中还是在磁盘上？ SQL Server 2005中是否有任何函数或工具可以测量表变量的规模或让我知道表变量何时从内存放入磁盘？
您的问题表明您已经屈服于有关表变量和临时表的一些常见误解。
我已经写了DBA 网站上有相当广泛的答案 https://dba.stackexchange.com/questions/16385/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server/16386#16386查看两种对象类型之间的差异。这也解决了您关于磁盘与内存的问题（我没有看到两者之间的行为有任何显着差异）。
关于标题中的问题，关于何时使用表变量与本地临时表，您并不总是有选择。例如，在函数中，只能使用表变量，如果需要在子作用域中写入表，则只能使用表变量#temp表就可以了
（表值参数允许只读访问 https://stackoverflow.com/questions/4626292/how-to-use-table-variable-in-a-dynamic-sql-statement/12876775#12876775).
如果您确实可以选择，下面列出了一些建议（尽管最可靠的方法是简单地根据您的特定工作负载测试两者）。
如果您需要一个无法在表变量上创建的索引，那么您当然需要一个#temporary桌子。然而，其细节取决于版本。对于 SQL Server 2012 及更低版本，可以在表变量上创建的唯一索引是通过隐式创建的索引UNIQUE or PRIMARY KEY约束。 SQL Server 2014 为可用选项的子集引入了内联索引语法CREATE INDEX。此后这一点已得到扩展，以允许过滤索引条件。索引与INCLUDE但是，仍然无法在表变量上创建 -d 列或列存储索引。
如果您要从表中重复添加和删除大量行，请使用#temporary桌子。那支持TRUNCATE（这比DELETE对于大表）以及随后的后续插入TRUNCATE可以比遵循以下方法的人有更好的表现DELETE 如图所示 https://dba.stackexchange.com/questions/27309/why-does-delete-leave-a-lingering-effect-on-performance/27319#27319.
- 如果您要删除或更新大量行，那么临时表的性能可能会比表变量好得多 - 如果它能够使用行集共享（请参阅下面的“行集共享的影响”示例）。
- 如果使用该表的最佳计划将根据数据而变化，则使用
#temporary桌子。支持创建统计信息，允许根据数据动态重新编译计划（尽管对于存储过程中的缓存临时表）重新编译行为 http://web.archive.org/web/20180130201242/http://sqlblog.com/blogs/paul_white/archive/2012/08/15/temporary-tables-in-stored-procedures.aspx需要单独理解）。
- 如果使用表的查询的最佳计划不太可能改变，那么您可以考虑使用表变量来跳过统计信息创建和重新编译的开销（可能需要提示来修复您想要的计划）。
- 如果插入表的数据源来自潜在昂贵的
SELECT语句然后考虑使用表变量将阻止使用并行计划的可能性。
- 如果您需要表中的数据在外部用户事务回滚后仍然存在，那么请使用表变量。一个可能的用例可能是记录长 SQL 批处理中不同步骤的进度。
- 当使用
#temp用户事务中的表锁可以比表变量持有更长时间（可能直到事务结束或语句结束，具体取决于锁的类型和隔离级别），并且它还可以防止截断tempdb事务日志直到用户事务结束。因此这可能有利于使用表变量。
- 在存储例程中，表变量和临时表都可以被缓存。缓存表变量的元数据维护比
#temporary表。鲍勃·沃德在他的tempdb推介会 http://www.youtube.com/watch?v=SvseGMobe2w在高并发的情况下，这可能会导致系统表出现额外的争用。此外，在处理少量数据时，这可以使性能上有可衡量的差异 https://dba.stackexchange.com/a/13412/3690.
行集共享的影响
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
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
