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的执行范围是single 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屋！
