问CTE、子查询、临时表或表变量之间是否存在性能差异？

EN

SQL是一种声明性语言，而不是过程性语言。也就是说，您可以构造一条SQL语句来描述所需的结果。您没有告诉SQL引擎如何完成这项工作。

一般来说，让SQL引擎和SQL优化器找到最佳查询计划是一个好主意。开发SQL引擎需要花费很多人的时间，所以让工程师去做他们知道该怎么做的事情吧。

当然，在某些情况下，查询计划不是最优的。然后，您需要使用查询提示、重构查询、更新统计信息、使用临时表、添加索引等，以获得更好的性能。

至于你的问题。理论上，CTE和子查询的性能应该是相同的，因为它们向查询优化器提供了相同的信息。一个不同之处在于，使用多次的CTE可以很容易地识别和计算一次。然后可以多次存储和读取结果。不幸的是，SQL Server似乎没有利用这种基本的优化方法(您可以将其称为常见子查询消除)。

临时表则是另一回事，因为您提供了有关如何运行查询的更多指导。一个主要区别是优化器可以使用临时表中的统计信息来建立其查询计划。这可以带来性能提升。此外，如果您有一个多次使用的复杂CTE (子查询)，那么将其存储在临时表中通常会提高性能。查询只执行一次。

您的问题的答案是，您需要尝试以获得预期的性能，特别是对于定期运行的复杂查询。在理想情况下，查询优化器将找到完美的执行路径。虽然经常如此，但您也许能够找到一种方法来获得更好的性能。

没有规则。我发现CTE更具可读性，除非它们表现出一些性能问题，否则我会使用它们，在这种情况下，我会调查实际问题，而不是猜测CTE是问题所在，并尝试使用不同的方法重写它。除了我选择用查询声明我的意图的方式之外，通常还有更多的问题。

当然，在某些情况下，您可以解开CTE或删除子查询，并将其替换为#temp表并减少持续时间。这可能是由于各种原因造成的，比如陈旧的统计数据、甚至无法获得准确的统计数据(例如，连接到表值函数)、并行性，甚至由于查询的复杂性而无法生成最佳计划(在这种情况下，拆分查询可能会给优化器一个机会)。但也有一些情况下，创建#temp表所涉及的I/O可能会超过其他性能方面，这可能会使使用CTE的特定计划形状变得不那么吸引人。

老实说，有太多的变量来为你的问题提供一个“正确”的答案。没有可预测的方法来知道查询何时可能倾向于一种或另一种方法-只需知道，在理论上，CTE或单个子查询的相同语义应该执行完全相同的。我认为如果您提出一些不正确的情况，您的问题会更有价值--可能是您在优化器中发现了一个限制(或者发现了一个已知的限制)，或者可能是您的查询在语义上不等价，或者您的查询包含了阻碍优化的元素。

因此，我建议您以一种对您来说最自然的方式编写查询，只有当您发现优化器有实际的性能问题时才会这样做。就我个人而言，我将它们排序为CTE，然后是子查询，而#temp table是最后的手段。

#temp是材料化的，而CTE不是。

CTE只是一个语法，所以理论上它只是一个子查询。它会被执行。#temp被物化。因此，在#temp中执行多次连接时，使用代价高昂的CTE可能更好。另一方面，如果它是一个简单的评估，不执行几次，那么就不值得#temp的开销。

有些人不喜欢表变量，但我喜欢它们，因为它们是物化的，创建起来比#temp更快。有时，与表变量相比，查询优化器使用#temp会做得更好。

在#temp或表变量上创建PK的能力为查询优化器提供了比CTE更多的信息(因为您不能在CTE上声明PK )。