SQL Server中的临时表使用情况

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

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

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

这是普遍意见还是有人反对意见？

临时表对于复杂的批处理过程(如报表或ETL作业)最有用.通常,您希望在事务性应用程序中很少使用它们.

如果您正在使用涉及多个大型表(可能是报表)的连接进行复杂查询,则查询优化器实际上可能无法在一次命中中对此进行优化,因此临时表在此处成为胜利 - 它们将查询分解为一系列更简单的一些,使查询优化器更少有机会搞砸计划.有时您的操作根本无法在单个SQL语句中完成,因此需要多个处理步骤才能完成工作.我们再次讨论更复杂的操作.

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

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

需要注意的一点是,临时表与查询引擎用于存储中间连接结果的结构完全相同,因此使用它们不会有性能损失.临时表还允许使用set操作的多阶段任务,并且在T-SQL代码中几乎(几乎不是)几乎不需要游标.

'Code Smell'是一种夸大其词但如果我看到很多涉及临时表的简单操作,我会想知道发生了什么.

这真的取决于你在做什么.我通常会尽量避免它们,但有时你需要做一些复杂的事情,需要多个步骤.通常这超出了从表格中选择的简单方法.像其他任何东西一样,它是一个你必须知道何时使用的工具.

我同意你的观点,我通常会让db处理幕后的东西,但有时候它的优化是关闭的,你必须亲自去做.