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

2021-12-02 00:00:00 sql-server temp-tables table-variable

我正在了解表变量的更多细节.就是说临时表总是在磁盘上，表变量在内存中，也就是说表变量的性能比临时表好，因为表变量比临时表使用的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 后面的插入可以比后面的插入有更好的性能DELETE 如此处所示.

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

相关文章