logo标签列表

SQL Server中临时表的使用

sqlsql-servert-sqlstar-schema
6

这是一个相对开放的问题，我非常想听听大家的意见。

我很少使用显式声明的临时表（无论是表变量还是常规#tmp表），因为我认为这样做会导致T-SQL更简洁、易读和易于调试。我还认为，在需要时（例如在查询中使用派生表时），SQL可以更好地利用临时存储。

唯一的例外是当数据库不是典型的关系数据库而是星型或雪花模式时。我了解到最好首先对事实表应用过滤器，然后使用结果临时表从维度中获取值。

这是普遍的观点还是有人持有反对意见？

6个回答
15

临时表最适合处理复杂的批处理任务，例如报表或ETL作业。通常情况下，在事务性应用程序中使用它们的频率较低。

如果您正在执行涉及多个大型表的联接的复杂查询（例如报表），查询优化器可能无法一次性地对其进行优化，因此在这里使用临时表是一个好方法 - 它将查询分解为一系列较简单的查询，从而减少了查询优化器出错的机会。有时您需要执行的操作根本无法在单个SQL语句中完成，因此需要多个步骤来完成任务。再次强调，这里涉及到更复杂的操作。

您还可以为中间结果创建临时表，然后对表进行索引，甚至可以将聚集索引放在其中以优化后续查询。这也可能是在不允许向数据库架构添加索引的系统上优化报告查询的快速和简单的方法。SELECT INTO非常有用，因为它的记录最少（因此快速），并且不需要将选择和插入的列进行对齐。

其他原因可能包括使用CROSS APPLY和xpath查询从XML字段中提取数据。通常，将其提取到临时表中，然后对临时表进行操作要更有效率。对于某些任务，它们比CTE更快，因为它们实现了查询结果而不是重新评估查询。

值得注意的一点是，临时表与查询引擎用于存储中间联接结果的结构完全相同，因此使用它们不会带来性能损失。临时表还允许使用集合操作进行多阶段任务，并使游标在T-SQL代码中几乎（不是完全）不必要。

“代码坏味”有点夸张，但如果我看到大量涉及临时表的简单操作，我会想知道发生了什么。

5

这完全取决于您要做什么。我通常尽量避免使用它们，但有时您需要执行一些复杂的操作，需要多个步骤。通常，这远远超出了简单的从表中选择的范畴。像其他工具一样，您必须知道何时使用它。

我同意您的看法，通常我会让数据库在后台处理数据，但有时优化并不好，您需要手动处理。

你是正确的。当情况变得复杂，直接使用 SQL 无法执行操作，而临时表可以，那么你会怎么做？ - John Mo
在中间层处理复杂的事情。 - Glenn
2
你的意思是像汇总数百万条记录吗？ - kemiller2002
3

我认为临时表是一种SQL代码异味，仅在最后一种可能的情况下使用。如果您需要在获得最终结果集之前缓存数据，则通常表示我对数据库设计不满意。

那也是我的观点，但我只是想知道我是否太挑剔了！ - Chris Simpson
2
需要注意的一点是，糟糕的数据库设计比你想象的要普遍得多。如果你想体验处理糟糕的数据库设计后果的乐趣，可以尝试在数据仓库领域工作几年。 - ConcernedOfTunbridgeWells
3

临时表在正确使用的情况下具有适当的用途，如果使用得当，它们不是代码异味。其中一个好处是，它们存在于tempdb中，而tempdb通常设置为简单恢复模式。这意味着，如果您正在使用临时表进行其擅长的操作（大多数为批量操作），则与在生产数据库中的表执行相同操作相比，您所生成的日志最小。

如果像另一个帖子建议的那样，您的生产数据库设备非常好，但是您的tempdb不好，请请求DBA将其移动。SQL Server本身使用tempdb来处理查询，因此对于tempdb来说，拥有高性能的环境至关重要。

表变量是完全不同的东西。它们仅存在于内存中。其中一个好的用途是，如果您需要为查询中的每一行调用带有CROSS APPLY的函数。如果该函数很昂贵，但是可以从中获得的不同结果数量很少，则通过预先计算所有可能调用的结果（或者也许是数据集的所有可能调用），并将其存储在表变量中，然后连接到该表变量，而不是使用CROSS APPLY，您可能会获得更高的性能。

3
+1 很好的回答，但是这句话并不正确：“表变量是完全不同的东西。 它们仅存在于内存中。” 这是一个谬论。它们在 tempdb 中分配空间。 - Martin Smith
@Martin - 很正确！通过《Inside Microsoft SQL Server 2008: T-SQL Programming》进行了验证，感谢您让我知道！ - Bryn Keller
0
我也避免使用临时表。据我所知，MS SQL Server上的临时表始终位于主数据库的文件组中。这意味着，尽管你的生产应用程序表很可能位于昂贵的高性能RAID设置上，但你的临时表位于MS SQL Server安装的位置，最有可能是在Program Files目录下的C:驱动器上。
0

当您有一个数据集需要在后续语句中检索一次并重复使用时，这也非常有用。

使这些长时间的批处理过程更易读（有时这比性能更重要）。

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接