是否替换#TEMP表？

问题描述：

因此，基于关我见过WITH使用方式，以及documentation at MSDN：是否替换#TEMP表？

指定临时命名的结果集，称为公用表表达式（CTE）。

看来WITH是#TEMP表的替代品。那是对的吗？

CTE（'WITH..'）实际上比表格更像临时视图。但它们实际上只是您可以别名的子查询（即为其分配名称） – RBarryYoung 2013-02-12 17:31:58

答

编号CTE - 由WITH引入 - 不会替换临时表，尽管在某些情况下可以在过去可能使用临时表的情况下使用它们。

WITH实际上只不过是一个派生表，不同之处在于它在查询而不是内联之前引入，并且给出了一个别名，然后可以在整个查询中多次使用该别名。

派生表是一个完整的查询，在括号内部，它就像是一个真正的表一样使用。视图和表值函数也被视为派生表，但我们专注于内联定义的类型。这里有一个例子：

SELECT 
    C.Name, 
    S.SalesTotal 
FROM 
    dbo.Customer C 
    INNER JOIN (
     SELECT 
     O.CustomerID, 
     SalesTotal = Sum(OrderTotal) 
     FROM 
     dbo.CustomerOrder O 
     GROUP BY 
     O.CustomerID 
    ) S 
     ON C.CustomerID = S.CustomerID;

我们必须返回自己的行集（该GROUP BY查询）完全完整的查询。通过将其置于圆括号内并为其分配一个别名S，我们现在可以像使用表一样使用它。我们可以加入更多的表格。但是，我们只加入了这张桌子一次。

将其转换为一个CTE，我们做一个很简单的变化：

WITH SalesTotals AS (
    SELECT 
     O.CustomerID, 
     SalesTotal = Sum(OrderTotal) 
    FROM 
     dbo.CustomerOrder O 
    GROUP BY 
     O.CustomerID 
) 
SELECT 
    C.Name, 
    S.SalesTotal 
FROM 
    dbo.Customer C 
    INNER JOIN SalesTotals S 
     ON C.CustomerID = S.CustomerID 
    -- and for an example of using the CTE twice: 
    INNER JOIN (
     SELECT Avg(SalesTotal) 
     FROM SalesTotals 
    ) A (AverageSalesTotal) 
     ON S.SalesTotal >= A.AverageSalesTotal;

现在，临时表是一个完全不同的动物。它已经从一个CTE或派生表非常重要的区别：

一个临时表持续超过许多查询（客户端连接的生命周期，或直到显式删除），但CTE只“存在”为一个查询。
如果在查询中多次使用CTE，则逻辑上为“单个”表的CTE可能会多次生成其数据。临时表的数据可以简单地读作其他任何“真实”表。在上面的示例中，在至少2012年的SQL Server版本中，Avg(SalesTotal)计算将涉及一次完全独立的操作，即第二次执行聚合。尽管引擎可以实现CTE的结果，但到目前为止，SQL Server还没有做到这一点。值得注意的是，其他DBMS如Oracle可能会实现CTE的结果。无论如何，您应该意识到，这种双重查询可能会（当然！）严重的性能影响。
临时表具有为其自动生成的列统计信息，这可以帮助查询优化器选择更好的执行计划。 CTE的“最终”行集没有统计信息 - 使用基础表的统计信息。
临时表可以增量添加或通过多条或重复语句从中删除行。它可以更新。
可以修改临时表以添加或删除列或更改数据类型。
临时表可以具有聚簇和非聚簇索引和约束。
您不能在用户定义的函数内以任何方式使用临时表。
一个CTE，虽然似乎逻辑上隔离查询的一部分，但没有这样的事情。 CTE是判断下推的完美候选者，如果确定它们不影响最终行集（或消除其某些表或联接），则它们是完美的候选对象，或者它们可能会受到意想不到的表达式评估顺序的影响。例如，在CTE中，您可能只返回文本列中的数字字符串，并且在外部查询中尝试将这些字符串转换为数字数据类型，但出乎意料的是，您会遇到尝试转换非数字字符串的错误到数字数据类型。这是因为优化器可以自由地以任何方式重新组织查询，并且可以在包含数字的字符串的过滤器之前将其转换为数字。临时表虽然需要两条语句（一条插入数据，另一条插入数据），但不会产生这个问题，因为查询是独立的，并且数据在使用之前真正实现了“物化”。

最后，CTE可以做临时表不能做的事情：它可以是递归的。在Oracle中，这通过0表示，并且在SQL Server中，它在CTE内部用UNION ALL SELECT完成，允许引用CTE自己的别名。

小心CTE - 它们是一个很好的抽象，但没有什么比这更重要，你可能会遇到严重的麻烦。生成一百万行可以用递归CTE一次一行地完成，但是这是最糟糕的方法，可能会超过一百倍甚至更多。

在SQL Server 2005中的另一个特殊的临时表和最多称为“表变量”，这是非常非常像一个临时表（并保持在tempdb完全一样），有几个明显的例外：

它仅持续批次的持续时间，而不是连接。
您可以在用户定义的函数中使用表变量。某些类型的UDF需要一个。
它只能声明内联约束（如主键或唯一性），虽然它可以更新/插入/删除行，但其声明后无法以任何方式修改其架构，因此不需要添加/删除列，更改数据类型或添加索引。
它不收集统计数据。
它可以作为参数（表值参数）在SQL Server 2008及更高版本中传递。

+1。除了“括号内”外，还有其他种类的派生表。 – RBarryYoung 2013-02-12 17:34:53

请您分享那些派生表，@RBarryYoung？ – ErikE 2013-02-12 17:37:41

视图和表值函数是目前我能想到的唯一的其他人。 – RBarryYoung 2013-02-12 17:38:27

答

不，这是不正确的。它们都是独立的功能块，每个都有其独特的用途。

例如，CTE适用于小数据位，而临时表通常更适合大数据集。临时表可以被编入索引，并且它们的性能得到改进，而CTE则不能。

我会花一点时间读通过MSDN文档和查看特定的情况下，您会使用一个或另一个

答

SQL优化今天做了更好的工作，在选择卓越的执行计划，但在加入时超过10个表格，特别是对于一些大型表格和视图，并且需要使用多个过滤器时，它通常不会达到最佳效果。我仍然发现没有什么比使用#TEMP表格更快，并且在将它们连接在一起之前将查询分解为更小的子集。注意：我发现向#TEMP表中添加索引可以提高性能的情况非常罕见。

是否替换#TEMP表？

相关推荐