SQL是一种声明性语言，而不是过程性语言。也就是说，您构造一个SQL语句来描述您想要的结果。您并没有告诉SQL引擎如何执行这项工作。

作为一般规则，让SQL引擎和SQL优化器找到最佳查询计划是个好主意。开发SQL引擎需要许多人多年的努力，所以让工程师做他们知道如何做的事情。

当然，也有查询计划不是最优的情况。然后，您希望使用查询提示、重新构造查询、更新统计信息、使用临时表、添加索引等来获得更好的性能。

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

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

您的问题的答案是，您需要尝试以获得您期望的性能，特别是对于定期运行的复杂查询。在理想的情况下，查询优化器会找到完美的执行路径。尽管它经常这样做，但您可能能够找到一种方法来获得更好的性能。

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

There are certainly cases when you can unravel CTEs or remove subqueries and replace them with a #temp table and reduce duration. This can be due to various things, such as stale stats, the inability to even get accurate stats (e.g. joining to a table-valued function), parallelism, or even the inability to generate an optimal plan because of the complexity of the query (in which case breaking it up may give the optimizer a fighting chance). But there are also cases where the I/O involved with creating a #temp table can outweigh the other performance aspects that may make a particular plan shape using a CTE less attractive.

Quite honestly, there are way too many variables to provide a "correct" answer to your question. There is no predictable way to know when a query may tip in favor of one approach or another - just know that, in theory, the same semantics for a CTE or a single subquery should execute the exact same. I think your question would be more valuable if you present some cases where this is not true - it may be that you have discovered a limitation in the optimizer (or discovered a known one), or it may be that your queries are not semantically equivalent or that one contains an element that thwarts optimization.

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

#temp是物化的，CTE不是。

CTE只是语法，所以理论上它只是一个子查询。执行。#temp被物化。因此，在多次执行的连接中使用昂贵的CTE可能在#temp中更好。另一方面，如果它是一个简单的计算，但没有执行几次，那么就不值得使用#temp。

有一些人在SO上不喜欢表变量，但我喜欢它们，因为它们是物质化的，创建速度比#temp快。有些时候，查询优化器使用#temp比使用表变量做得更好。

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

我认为使用# Temp表比使用CTE更可取的两件事是:

您不能在CTE上放置主键，因此CTE访问的数据必须遍历CTE表中的每个索引，而不是仅仅访问临时表上的PK或Index。 因为不能向CTE添加约束、索引和主键，它们更容易出现错误和坏数据。

-onedaywhen昨天

这里有一个例子，#table约束可以防止坏数据，这不是CTE的情况

DECLARE @BadData TABLE ( 
                       ThisID int
                     , ThatID int );
INSERT INTO @BadData
       ( ThisID
       , ThatID
       ) 
VALUES
       ( 1, 1 ),
       ( 1, 2 ),
       ( 2, 2 ),
       ( 1, 1 );

IF OBJECT_ID('tempdb..#This') IS NOT NULL
    DROP TABLE #This;
CREATE TABLE #This ( 
             ThisID int NOT NULL
           , ThatID int NOT NULL
                        UNIQUE(ThisID, ThatID) );
INSERT INTO #This
SELECT * FROM @BadData;
WITH This_CTE
     AS (SELECT *
           FROM @BadData)
     SELECT *
       FROM This_CTE;