SQL Server中的临时表使用 [英] Temporary Table Usage in SQL Server

问题描述

这是一个悬而未决的问题，但我真的很想听听人们的意见.

This is a bit of an open question but I would really like to hear people opinions.

我很少使用显式声明的临时表(表变量或常规#tmp表)，因为我认为不这样做会导致更简洁，可读性和可调试性的T-SQL.我还认为，与需要临时存储(例如，在查询中使用派生表)时使用SQL相比，SQL可以做得更好.

I rarely make use of explicitly declared temporary tables (either table variables or regular #tmp tables) as I believe not doing so leads to more concise, readable and debuggable T-SQL. I also think that SQL can do a better job than I of making use of temporary storage when it's required (such as when you use a derived table in a query).

唯一的例外是，当数据库不是典型的关系数据库而是星型或雪花模式时.我知道最好先将过滤器应用于事实表，然后使用生成的临时表从维度中获取值.

The only exception is when the database is not a typical relational database but a star or snowflake schema. I understand that it's best to apply filters to the fact table first and then use the resultant temp table to get the values from your dimensions.

这是普遍看法还是有人反对?

Is this the common opinion or does anyone have an opposing view?

推荐答案

临时表对于诸如报告或ETL作业之类的复杂批处理最为有用.通常，您希望很少在事务性应用程序中使用它们.

Temporary tables are most useful for a complex batch process like a report or ETL job. Generally you would expect to use them fairly rarely in a transactional application.

如果您要使用包含多个大表(可能是一个报表)的联接进行复杂查询，则查询优化器实际上可能无法一口气对其进行优化，因此临时表在这里会很成功-它们会分解查询分成一系列简单的查询，这些查询使优化查询的人更少了搞砸计划的机会.有时您根本无法在单个SQL语句中完成某项操作，因此，要完成这项工作，必须执行多个处理步骤.同样，我们在这里谈论的是更复杂的操作.

If you're doing complex query with a join involving multiple large tables (perhaps for a report) the query optimiser may not actually be able to optimise this in one hit, so temporary tables become a win here - they decompose the query into a series of simpler ones that give the query optimiser less opportunity to screw up the plan. Sometimes you have an operation that cannot be done in a single SQL statement at all, so multiple steps for processing are necessary to do the job at all. Again, we're talking about more complex manipulations here.

您还可以为中间结果创建一个临时表，然后为该表建立索引，甚至可能在其上放置聚簇索引以优化后续查询.这也可能是在不允许向数据库架构添加索引的系统上优化报表查询的快速而肮脏的方法. SELECT INTO对于这种类型的操作非常有用，因为它的日志记录最少(因此速度很快)，并且不需要对齐select和insert的列.

You can also create a tempory table for an intermediate result and then index the table, possibly even putting a clustered index on it to optimise a subsequent query. This might also be a quick and dirty way to optimise a report query on a system where you are not allowed to add indexes to the database schema. SELECT INTO is useful for this type of operation as it is minimally logged (and therefore fast) and doesn't require to align the columns of a select and insert.

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

Other reasons might include extracting data from XML fields using CROSS APPLY and xpath queries. Generally it's much more efficient to extract this into a temp table and then work on the temp table. They're also much faster than CTE's for some tasks as they materialise the query results rather than re-evaluating the query.

要注意的一件事是，临时表的结构与查询引擎用于存储中间联接结果的结构完全相同，因此使用临时表不会造成性能损失.临时表还允许使用set操作进行多阶段任务，并使T-SQL代码中的游标几乎(不是，但几乎但几乎)不必要.

One thing to note is that temporary tables are exactly the same structure that the query engine uses to store intermediate join results, so there is no performance penalty to using them. Temporary tables also allow multi-phase tasks using set operations and make cursors almost (not quite but almost) unnecessary in T-SQL code.

代码气味"是一个夸大的说法，但是如果我看到很多涉及临时表的简单操作，我会想知道发生了什么.

'Code Smell' is an overstatement but if I saw a lot of simple operations involving temporary tables I would be wondering what was going on.

这篇关于SQL Server中的临时表使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！