为什么CTE比游标/派生表/子查询/临时表等更好？

本文介绍了为什么CTE比游标/派生表/子查询/临时表等更好？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

与派生表/子查询/临时表等方法相比，CTE如何以及为什么具有更好的性能？

How and why CTE gives a better performance as compared to derived table/ subqueries/ temp table etc. approaches?

任何临时计算都会在临时数据库中进行。因此，如果我们使用游标方法，它还会在临时数据库中创建临时表/工作表，一旦操作结束，该工作表将被销毁。我对CTE的理解是，它也一样（或者是否在内存中创建了临时结果？因此提高了性能）为什么它比上面的方法（如游标/派生表/子查询/临时表等）更好？

Any temporary calculations happens in the temporary database. So if we have a cursor approach, it also creates temporary table/work table in the temporary database and once the operation is over, that work table is destroyed. My understanding of CTE is that, it also does the same(or does it creates temporary result in memory? and hence the performance improvement) Then why is it better than the above approaches like cursor/derived table/ subqueries/ temp table etc.?

推荐答案

A（非递归）CTE不使用游标。这是一种基于集合的方法。与使用游标相比，这是最大的不同。

A (non-recursive) CTE does not use cursors. It is a set based approach. That's the big difference compared to using cursors. But then that's true of not using cursors in general.

应该在绝对可能的情况下避免使用游标（因为我确定我们都知道）。

Cursors should be avoided where absolutely possible (as I'm sure we are all aware).

CTE不一定比使用派生表更好，但确实会导致更易于理解的TSQL代码。 CTE实际上只是查询或子查询的简写。类似于临时视图。

A CTE is not necessarily better than using a derived table, but does lead to more understandable TSQL code. A CTE is really just shorthand for a query or subquery; something akin to a temporary view.

CTE可能不是最佳方法的情况是查询计划优化程序获得的CTE行估计不准确。

The situation where CTE's might not be the best approach, is when the query plan optimiser gets inaccurate row estimates for the CTE.

相关问题：什么是优点/缺点使用CTE？