我什么时候应该在 sql server 中使用表变量和临时表? [英] When should I use a table variable vs temporary table in sql server?

问题描述

我正在了解更多关于表变量的详细信息.它说临时表总是在磁盘上，而表变量在内存中，也就是说，表变量的性能优于临时表，因为表变量使用的IO操作比临时表少.

I'm learning more details in table variable. It says that temp tables are always on disk, and table variables are in memory, that is to say, the performance of table variable is better than temp table because table variable uses less IO operations than temp table.

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

But sometimes, if there are too many records in a table variable that can not be contained in memory, the table variable will be put on disk like the temp table.

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

But I don't know what the "too many records" is. 100,000 records? or 1000,000 records? How can I know if a table variable I'm using is in memory or is on disk? Is there any function or tool in SQL Server 2005 to measure the scale of the table variable or letting me know when the table variable is put on disk from memory?

推荐答案

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

Your question shows you have succumbed to some of the common misconceptions surrounding table variables and temporary tables.

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

I have written quite an extensive answer on the DBA site looking at the differences between the two object types. This also addresses your question about disk vs memory (I didn't see any significant difference in behaviour between the two).

关于标题中关于何时使用表变量与本地临时表的问题，您并不总是有选择的余地.例如，在函数中，只能使用表变量，如果您需要在子范围内写入表，那么只有 #temp 表可以(表值参数允许 只读访问).

Regarding the question in the title though as to when to use a table variable vs a local temporary table you don't always have a choice. In functions, for example, it is only possible to use a table variable and if you need to write to the table in a child scope then only a #temp table will do (table-valued parameters allow readonly access).

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

Where you do have a choice some suggestions are below (though the most reliable method is to simply test both with your specific workload).

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

1. If you need an index that cannot be created on a table variable then you will of course need a #temporary table. The details of this are version dependant however. For SQL Server 2012 and below the only indexes that could be created on table variables were those implicitly created through a UNIQUE or PRIMARY KEY constraint. SQL Server 2014 introduced inline index syntax for a subset of the options available in CREATE INDEX. This has been extended since to allow filtered index conditions. Indexes with INCLUDE-d columns or columnstore indexes are still not possible to create on table variables however.

如果您要从表中重复添加和删除大量行，请使用 #temporary 表.它支持 TRUNCATE (对于大型表，它比 DELETE 更有效)，另外，在 TRUNCATE 之后的后续插入可以比在删除 如图所示.

If you will be repeatedly adding and deleting large numbers of rows from the table then use a #temporary table. That supports TRUNCATE (which is more efficient than DELETE for large tables) and additionally subsequent inserts following a TRUNCATE can have better performance than those following a DELETE as illustrated here.

行集共享的效果

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 server 中使用表变量和临时表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！