CTE和临时表之间有什么区别?


150

公共表表达式(CTE)和临时表之间有什么区别?我应该什么时候使用一个呢?

CTE

WITH cte (Column1, Column2, Column3) 
AS 
(
    SELECT Column1, Column2, Column3 
    FROM SomeTable 
) 

SELECT * FROM cte 

临时表

SELECT Column1, Column2, Column3 
INTO #tmpTable 
FROM SomeTable 

SELECT * FROM #tmpTable 
+3

另请参见[SQL Server中的临时表和表变量之间的区别?](http://dba.stackexchange.com/questions/16385/whats-the-difference-between-a-temp- table-and-table-variable-in-sql-server) 15 2月. 122012-02-15 17:02:17

+1

SO:[更高性能,CTE或临时表?](http://stackoverflow.com/a/698634/27535) 16 2月. 122012-02-16 07:52:38

178

这是相当宽泛的,但我会给你一般答案,我可以。

热膨胀系数...

  • 是unindexable(但可以使用引用的对象现有的索引)
  • 不能有限制
  • 基本上是一次性VIEW小号
  • 只持续到下一个查询运行
  • 可递归
  • 个没有专门的统计数据(依靠统计对基础对象)

#TEMP表...

  • 是真实的物化表存在于tempdb中
  • 可以被索引
  • 可以有限制
  • 坚持为目前的生活连接
  • 可以参考其他查询或子过程
  • 具有专用发动机

至于何时使用每种生成的统计信息,他们有很不同的使用情况。如果您将有一个非常大的结果集,或者需要多次引用它,请将它放在#temp表中。如果它需要递归,是一次性的,或者只是为了简化逻辑上的事情,那么CTE是首选。

此外,CTE应该永远不会用于性能。使用CTE几乎不会加快速度,因为再次,它只是一次性视图。你可以用它们做一些整洁的事情,但加速查询并不是真正的其中之一。


23

编辑:

请参考下面马丁的评论:

CTE没有物化为内存中的表。这只是封装查询定义的一种方式。在OP的情况下,它将被内联,与刚刚做的SELECT Column1, Column2, Column3 FROM SomeTable一样。大多数情况下,它们不会在前面实现,这就是为什么它不返回行WITH T(X) AS (SELECT NEWID())SELECT * FROM T T1 JOIN T T2 ON T1.X=T2.X,也检查执行计划。虽然有时可能破解计划以获得假脱机。有一个连接项要求提示。 - 马丁·史密斯2月15日在'12 17:08


原来的答复

CTE

Read more on MSDN

的CTE在内存中创建正在使用的表,但仅对其后的特定查询有效。当使用递归时,这可能是一个有效的结构。

您可能还想考虑使用表变量。这被用作作为使用临时表并且可以多次使用,而不需要为每次连接重新实现。另外,如果您现在需要保留几条记录,请在下一次选择后再添加几条记录,在另一条操作后添加几条记录,然后只返回那几条记录,然后这可以是一个方便的结构,因为它不会执行后不需要放弃。大多只是语法糖。但是,如果您保持行计数低,它永远不会实现到磁盘。有关更多详细信息,请参阅What's the difference between a temp table and table variable in SQL Server?

临时表

Read more on MSDN - Scroll down about 40% of the way

一个临时表实际上就是在磁盘上创建一个表,只是在一个特定的数据库大家都知道可以删除。一个好的开发人员有责任在不再需要时摧毁这些表,但DBA也可以将其清除。

临时表有两种:临时表和全局表。对于MS Sql Server,您使用#tableName指定本地,##tableName指定全局(注意使用单个或双个#作为标识特征)。

注意,对于临时表而言,与表变量或CTE相反,您可以应用索引等,因为这些是正常意义上的合法表。


一般我会用临时表较长或较大查询和热膨胀系数或表变量,如果我有一个小的数据集已经和想只是快速脚本了一些代码的小东西。他人的经验和建议表明,您应该使用CTE,在这些CTE中只有少量行被返回。如果你有一个很大的数字,你可能会受益于在临时表上索引的能力。

+11

CTE是没有物化为内存中的表格。这只是封装查询定义的一种方式。在OP的情况下,它将被内联,并且与刚做'SELECT Column1,Column2,Column3 FROM SomeTable'相同[ 15 2月. 122012-02-15 16:55:26

+4

]大多数情况下,它们并没有物化到前端,这就是为什么这不返回行'WITH T X)AS(SELECT NEWID())SELECT * FROM T T1 JOIN T T2 ON T1.X = T2.X',同时检查执行计划。尽管有时可以[破解计划](http://explainextended.com/2009/05/28/generating-xml-in-subqueries/)来获取假脱机文件。有一个[连接项](https://connect.microsoft.com/SQLServer/feedback/details/218968/provide-a-hint-to-force-intermediate-materialization-of-ctes-or-derived-tables)为此请求提示。 15 2月. 122012-02-15 17:08:33


12

可以在查询中重复调用CTE,并在每次引用时对其进行评估 - 此过程可以是递归的。如果它只是被引用一次,那么它的行为很像子查询,尽管CTE可以被参数化。

临时表物理上是持久的,并且可能被编入索引。在实践中,查询优化器也可能在后台持久化中间连接或子查询结果,例如在假脱机操作中,所以CTE的结果永远不会永久保存到磁盘。

IIRC表变量(另一方面)始终是内存中的结构。

+4

CTE可以参数化?怎么样?另外,表变量不总是*内存结构。请参阅Martin的[优秀回答](http://dba.stackexchange.com/a/16386/1192)以解答相关问题。 30 9月. 162016-09-30 06:14:36


10

临时表是tempdb中的一个真实对象,但cte只是一种围绕复杂查询的包装,可以简化组织递归的语法。


12

accepted answer这里说“CTE不应该用于表现” - 但这可能会误导。在CTE与临时表的关系中,我刚刚完成从一组存储过程中删除了一大堆垃圾,因为一些doofus一定认为使用临时表几乎没有开销。我把这批货推到了CTE中,除了那些在整个过程中合法地重新使用的那些。所有指标我都获得了约20%的表现。然后我开始着手删除所有尝试实现递归处理的游标。这是我看到最大收获的地方。我最终削减了10倍的响应时间。

CTE和临时表具有非常不同的用例。我只想强调一下,虽然不是万能药,但CTE的理解和正确使用可以在代码质量/可维护性和速度方面取得一些真正的显着提高。由于我掌握了它们,所以我将临时表和游标视为SQL处理的巨大祸害。我可以很好地利用表变量和CTE来处理几乎所有的事情。我的代码更干净更快。

  0

现在,让我们公平 - 游标是* great *邪恶;临时表是最糟糕的*邪恶。 :-)如果你看到你自己,那么把它们放在同一层次是非常不公平的。 22 1月. 182018-01-22 20:50:17


6

使用CTE的主要原因是访问窗口函数,如row_number()和其他各种。

这意味着您可以按照每组获得第一行或最后一行的方式非常快速高效地完成 - more efficiently than other means in most practical cases

with reallyfastcte as (
select *, 
row_number() over (partition by groupingcolumn order by sortingcolumn) as rownum 
from sometable 
) 
select * 
from reallyfastcte 
where rownum = 1; 

您可以运行类似的查询上面使用相关子查询或通过使用子查询,但CTE将在几乎所有情况下更快。

此外,CTE真的可以帮助简化您的代码。这可以提高性能,因为您更了解查询并可以引入更多业务逻辑来帮助优化器更具选择性。

此外,如果您了解业务逻辑并知道应该首先运行查询的哪些部分,CTE可以提高性能 - 通常,首先将最具选择性的查询放到可以在下一次连接中使用索引的结果集并添加option(force order)查询提示

最后,默认情况下,CTE不使用tempdb,因此通过使用它们可以减少对该瓶颈的争用。如果您需要多次查询数据

临时表应使用,或者如果你措施你的查询和发现,通过插入到一个临时表中,然后将你的性能得到改善的指标。

  0

所有优点... +1 30 1月. 182018-01-30 13:05:25


5

对CTE来说,这里似乎有点消极。

我对CTE的理解是它基本上是一种adhoc视图。 SQL既是一种声明式语言,也是一种基于集合的语言。 CTE是宣布一个集合的好方法!不能索引CTE实际上是一件好事,因为你不需要!这实际上是一种使查询更易于读取/写入的语法糖。任何像样的优化器都会使用底层表上的索引来制定最佳访问计划。这意味着您可以通过遵循基础表的索引建议来有效加速CTE查询。

另外,仅仅因为您将一个集合定义为CTE,并不意味着必须处理该集合中的所有行。根据查询,优化器可能会处理“足够”的行以满足查询。也许你只需要前20个左右的屏幕。如果你构建了一个临时表,那么你确实需要读/写所有这些行!

基于此,我会说CTE是SQL的一个很棒的功能,可以在查询更容易阅读的任何地方使用。我只会考虑一个真正需要处理每条记录的批处理过程的临时表。即使那时afaik也不推荐它,因为在临时表上,数据库很难帮助你缓存和索引。拥有一个永久性表格,其PK字段对于您的交易是唯一的可能会更好。

我不得不承认,我的经验主要是与DB2有关,所以我假设CTE的工作方式与这两种产品类似。如果CTE在SQL服务器方面逊色一些,我会很乐意纠正。 ;)