为什么CTE比游标/派生表/子查询/临时表等更好？
与派生表/子查询/临时表等方法相比，CTE 如何以及为何提供更好的性能？
任何临时计算都发生在临时数据库中。因此，如果我们有游标方法，它还会在临时数据库中创建临时表/工作表，一旦操作结束，该工作表就会被销毁。我对CTE的理解是，它也做同样的事情（或者它在内存中创建临时结果？从而提高性能）那么为什么它比上面的方法（如游标/派生表/子查询/临时表等）更好？
How and why CTE gives a better performance as compared to derived table/ subqueries/ temp table etc. approaches?
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.?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
（非递归）CTE 不使用游标。这是一种基于集合的方法。这是与使用游标相比的巨大区别。但一般情况下不使用游标也是如此。
应尽可能避免使用游标（我相信我们都知道）。
CTE 不一定比使用派生表更好，但确实会导致更易于理解的 TSQL 代码。 CTE 实际上只是查询或子查询的简写；类似于临时视图的东西。
当查询计划优化器获得不准确的 CTE 行估计时，CTE 可能不是最佳方法。
相关问题：使用 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).
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.
The situation where CTE's might not be the best approach, is when the query plan optimiser gets inaccurate row estimates for the CTE.
Related question: What are the advantages/disadvantages of using a CTE?