在CTE和临时表之间执行 [英] Performace between CTE and Temporary table

问题描述

对于哪种表现会很高。 SQL服务器中的CTE或临时表？



请注意这个

Hi,

For which performace will be high. CTE or Temporary table in SQL server?

Kindly advise on this

推荐答案

它们用于不同的目的。

CTE如果已实现则存储在内存中，并且只能在一个查询中使用。

临时表中的数据会持续存在，直到超出范围，因此可以被许多查询使用。

并且临时表可以编入索引。



所以你的问题的答案通常是CTE至少同样快或者更快，但例外情况是索引临时表使查询更快，以补偿持久化所需的额外资源。

They serve different purposes.
A CTE is stored in memory if it's materialized, and can only be used within one query.
The data in a temp table is persisted until it goes out of scope, and can therefore be used by many queries.
And a temp table can be indexed.

So the answer to your question would normally be that the CTE is at least as fast or faster, but the exception is when an indexed temp table makes a query so much faster that it compensates for the extra resources needed for persisting it.

你真的需要正确研究这个...这个论坛的主题是什么。



在sqlservercentral [ ^ ]关于主题，其中还包括指向其他​​文章的链接。

You really need to research this properly ... it's sort of off-topic for this forum.

There is a discussion over at sqlservercentral[^] on the subject which also includes links to other articles.

这篇关于在CTE和临时表之间执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！