CTE和TEMP表的主要区别是什么？

Question

使用 CTE（公共表达式）与使用 临时表 相比，有什么好处呢？

我对两者进行了性能测试，但我发现它们之间没有太大的区别。

那么使用 CTE 有哪些优缺点呢？

- GreatLakes07

---

从DBA网站上CTE和临时表有什么区别？： - Martin Smith

我不太了解性能差异，但是CTE允许简单的循环和递归，如果查询输出将被多次访问并且重新运行会浪费时间，则临时表非常方便。 - Hart CO

它们是不同的东西，实际上这取决于具体情况。哪个更好将取决于您的确切场景。你的问题并不比“使用Views而不是tables有什么好处？”明确得多。 - GarethD

CTE的性能也较慢，因为结果不会被缓存。所以每次使用CTE时都会重新运行查询、计划和所有操作。 - vhadalgi

这是一个重要的观点，特别是当CTE/TT处理数百万行数据时。 - spinjector

查询计划可能会过时，这种情况下使用CTE将是一个好处。 - todji

Answer 1

CTE和临时表最大的区别可能在于，CTE的执行范围仅限于单个SELECT、INSERT、UPDATE、DELETE或CREATE VIEW语句。

实际上，不能像使用临时表那样重用CTE。

根据文档：

通用表达式（CTE）可以看作是一个在单个SELECT、INSERT、UPDATE、DELETE或CREATE VIEW语句的执行范围内定义的临时结果集。与派生表相似，CTE不作为对象存储，并且只在查询期间存在。与派生表不同的是，CTE可以自引用，并且可以在同一查询中多次引用。

CTE可用于以下方面：

1. 创建递归查询。有关更多信息，请参阅使用通用表达式进行递归查询。

2. 当不需要一般视图的使用时，可以替代视图；也就是说，您不必将其定义存储在元数据中。

3. 使分组由标量子查询或具有非确定性或外部访问的函数导出的列成为可能。

4. 在同一语句中多次引用结果表。

- bastos.sergio

---

对我来说，最大的区别是一个是实际的表格，而另一个不是。WITH CTE AS (SELECT * FROM MillionRowTable) SELECT TOP 1 * FROM CTE和INSERT INTO #T SELECT * FROM MillionRowTable; SELECT TOP 1 * FROM #T之间有很大的区别。 - Martin Smith

@MartinSmith 您的评论比答案本身获得了更多的投票！！ - ebram khalil

它也可以反过来：insert into #table 后跟 select .. where xyz not in #table 是快速的，但重写为 CTE 会使其变得非常慢。 - Roman Starkov

Answer 2

CTE：CTE代表通用表达式。它是在SQL Server 2005中引入的。它用于在临时基础上存储复杂子查询的结果。其寿命仅限于当前查询。它通过使用WITH语句进行定义。它主要用于递归调用。

示例：

;with myCTE as 
(
    select ParentLevel, ParentID, ChildID 
    from MHA 
    where ChildID = 1 
    UNION ALL
    select MHA.ParentLevel, MHA.ParentID, MHA.ChildID 
    from MHA
    inner join myCTE on MHA.ParentID = myCTE.ChildID
    where MHA.ParentID <> 0
)

（错误）

select top (5) * from myCTE

在上面的例子中，我创建了一个名为myCTE的CTE，它只能在上面的查询中使用（我不能在上面的查询之外使用myCTE）。

TEMP: 它也用于在临时基础上存储查询结果。但是其生命周期仅限于当前会话。它是通过使用#来定义的。它不支持递归。

示例：

select * into #tempTable from MHA

在上面的查询中，我创建了临时表，现在我可以在这个查询之外但是在会话内使用它。如下所示：

(没有错误)

select top (5) * from #tempTable

- Sonu Rajpoot