sql-server - 是用#TEMP表的替代品吗？

Question

因此，根据我看到的WITH和documentation at MSDN的使用方式：


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


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

Answer 1

不可以。由WITH引入的CTE不能代替临时表，尽管在某些情况下可以在过去使用临时表的地方使用它们。

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在逻辑上是“单个”表，但如果在查询中使用多次，则可能会多次生成其数据。临时表的数据将像其他任何“真实”表一样被读取。在上面的示例中，在至少到2012年的SQL Server版本中， Avg(SalesTotal)计算将涉及完全独立的操作，该操作将第二次执行 SalesTotals聚合。尽管引擎有可能实现CTE的结果，但到目前为止，SQL Server尚未做到这一点。值得注意的是，其他DBMS（例如Oracle）可能会实现CTE的结果。无论如何，您应该意识到这种双重查询可能会（当然！）严重影响性能。
临时表具有为其自动生成的列统计信息，这可以帮助查询优化器选择更好的执行计划。 CTE的“最终”行集没有统计信息-使用基础表的统计信息。
可以临时添加临时表，也可以通过多个或重复的语句从中删除行。可以更新。
可以修改临时表以添加或删除列或更改数据类型。
临时表可以具有集群索引和非集群索引以及约束。
您不能在用户定义的函数内以任何方式使用临时表。
CTE看起来似乎在逻辑上将查询的各个部分隔离开来，但却没有做到这一点。 CTE非常适合谓词下推，消除谓词，如果确定它们不影响最终行集（或消除了某些表或联接），或者它们可能受到意外的表达式评估顺序。例如，在CTE中，您可能只返回文本列中的数字字符串，而在外部查询中尝试将这些字符串转换为数字数据类型，但是令人惊讶的是，您收到有关尝试转换非数字字符串的错误转换为数字数据类型。这是因为优化器可以自由地以自己喜欢的任何方式重组查询，并且可以在过滤包含数字的字符串之前将其转换为数字。临时表虽然需要两个语句（一个语句插入数据，第二个语句联接到该数据），却不会出现此问题，因为查询是截然不同的，并且在使用数据之前，数据确实如预期的那样“物化”。


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

注意CTE-它们是很好的抽象，但仅此而已，您可能会遇到严重的麻烦。可以通过递归CTE一次生成一百万行，但这是最糟糕的方式，可能是一百倍以上。

SQL Server 2005及更高版本中还有另一种特殊的临时表，称为“表变量”，它非常类似于临时表（并在tempdb中完全相同），但有一些值得注意的例外：


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