CTE和TEMP表之间的主要区别是什么? [英] What are the Main difference between CTE's and TEMP tables?
问题描述
使用 CTE的(公用表表达式)代替使用 temp表是否有好处.
Is there a benefit to using CTE's (common table expressions) instead of using temp tables.
我经历了两者之间的性能测试，但是我发现两者之间没有太大区别.
I went through performance testing between both of them, but I cant find much difference between them.
使用 CTE'S 的利弊是什么?
推荐答案
CTE和临时表之间的最大区别可能是CTE的执行范围为单 SELECT，INSERT，UPDATE，DELETE或CREATE VIEW语句.
Probably the biggest difference between a CTE and a temp table, is that the CTE has an execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.
基本上，您不能像使用临时表那样重复使用CTE.
Essentially you can't reuse the CTE, like you can with temp tables.
从文档
可以将公用表表达式(CTE)视为临时表在单个执行范围内定义的结果集SELECT，INSERT，UPDATE，DELETE或CREATE VIEW语句.CTE是与派生表类似，因为它不存储为对象，并且仅在查询期间持续.与派生表不同，CTE可以自我引用，并且可以多次引用相同的查询.
A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.
CTE可用于:
-
创建一个递归查询.有关更多信息，请参见使用公用表表达式的递归查询.
Create a recursive query. For more information, see Recursive Queries Using Common Table Expressions.
在不需要通用视图的情况下替代视图；也就是说，您不必将定义存储在元数据中.
Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
启用按标量子选择或不确定性或具有外部访问权限的函数派生的列进行分组的功能.
Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
在同一条语句中多次引用结果表.
Reference the resulting table multiple times in the same statement.
这篇关于CTE和TEMP表之间的主要区别是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
