CTE是否在tempdb中使用任何空间？ [英] Do CTEs use any space in tempdb?

问题描述

CTE是否在tempdb中使用任何空间，还是仅使用内存？

Do CTEs use any space in tempdb or does it use memory exclusively?

我在同时使用mssql 2005和2008标记了这个问题。 / p>

I've tagged the question with both mssql 2005 and 2008 as I use both.

推荐答案

我将尽量不要复制/粘贴MSDN

I'll try not to copy/paste MSDN

它没关系。

CTE与查询执行无关：它只是一种语言构造。将其视为整洁的派生表或子查询。

A CTE is independent of query execution: it is only a language construct. Think of it as neat derived table or subquery.

这意味着除递归CTE（请参阅下文）外，所有 CTE都可以内联编码。如果您一次使用CTE代码，则是为了可读性。如果您使用CTE两次或两次以上，那么这是防御性的：您不想犯一个错误，并且每次使用派生表都不相同。

This means that except for recursive CTEs (see later), all CTEs can be coded inline. If you use the CTE code once, it is for readability. If you use the CTE twice or more, then it is defensive: you don't want to make a mistake and have the derived table different each use.

其中CTE如果使用两次或更多次，则该代码将被执行两次或更多次。

Where a CTE is used twice or more, then that code will be executed twice or more. It won't be executed once and cached in tempdb.

摘要：它可能会也可能不会，就像代码是内联的一样。

Summary: it may or may not, just like if the code was inline.

注意：递归CTE只是der内的派生表内的派生表内的派生表内的派生表...所以同样适用。

Note: a recursve CTE is simply a derived table inside a derived table inside a derived table inside a a derived table inside a der... so same applies.

您可以在 Tony Rogerson的文章。如果内联编码，tempdb的使用还是会发生。他还指出，使用临时表可能会更好，因为我在上面解释了宏扩展

You can see this in Tony Rogerson's article. The use of tempdb would happen anyway if coded inline. He also notes that using a temp table can be better because of the "macro" expansion I explained above

仅供参考：对于视图也是如此。只是宏。

FYI: the same applies to views. Just macros.

这篇关于CTE是否在tempdb中使用任何空间？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！