是用#TEMP表的替代品吗？ [英] Is WITH the replacement for a #TEMP table?

问题描述

所以基于我所见过的 WITH 和在MSDN上的文档：

So based off the way I've seen WITH used, and the documentation at MSDN:

指定一个临时命名结果集，称为

Specifies a temporary named result set, known as a common table expression (CTE).

看来 WITH 是 #TEMP 表的替换。

推荐答案

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

No. CTEs--introduced by WITH--don't replace temp tables, although in some cases they can be used where one might have used a temp table in the past.

WITH 实际上只是一个派生表，不同之处在于它是在查询之前而不是内联引入的，

WITH is really nothing more than a derived table, with the difference that it is introduced before the query instead of inline, and is given an alias which can then be used as a table throughout the query multiple times.

派生表是一个完整的查询，位于圆括号内，用作别名。如果它是一个真实的表。视图和表值函数也被视为派生表，但我们专注于内联定义的类型。下面是一个示例：

A derived table is a complete query, inside of parentheses, that is used as if it were a real table. Views and table-valued functions are also considered derived tables, but we're focusing on the kind that is defined inline. Here is an example:

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 ，我们现在可以像表一样使用它。我们可以将更多表加入该表。但是，我们只加入了此表一次。

We have a completely intact query that returns its own rowset (the GROUP BY query). By placing this inside of parentheses and assigning it an alias S, we can now use it like a table. We could join more tables to this one. But, we have only joined to this table once.

要将其转换为CTE，我们做了一个非常简单的更改：

To convert this to a CTE, we make a very simple change:

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或派生表有非常重要的区别：

Now, a temp table is a completely different animal. It has very important differences from a CTE or derived table:

• 临时表在许多查询中都存在（在客户端连接的生命周期内，或直到被明确删除），但一个查询仅存在。


• 一个CTE，虽然逻辑上是单个表，但如果多次使用，则可能多次生成其数据。查询次数。临时表的数据将像其他任何真实表一样被读取。在上面的示例中，在至少到2012年的SQL Server版本中， Avg（SalesTotal）计算将涉及执行的完全独立的操作。 SalesTotals 第二次汇总。尽管引擎有可能实现CTE的结果，但到目前为止，SQL Server尚未做到这一点。值得注意的是，其他DBMS（例如Oracle）可能会实现CTE的结果。无论如何，您应该意识到，这种双重查询可能（当然！）会严重影响性能。


• 临时表会自动为其生成列统计信息，这可以帮助查询优化器来选择更好的执行计划。 CTE的最终行集没有统计信息-使用了基础表的统计信息。


• 可以将临时表增量添加或通过多个或重复的语句从中删除行。可以对其进行更新。


• 可以修改临时表以添加或删除列或更改数据类型。


• 临时表可以具有群集和非聚集索引和约束。


• 您不能在用户定义的函数内以任何方式使用临时表。


• CTE出现时逻辑上分离查询的各个部分，则不会这样做。如果确定CTE不影响最终行集（或消除了某些行或联接），则它们是谓词下推，消除的理想人选，否则它们可能会受到意料之外的表达式求值顺序。例如，在CTE中，您可能只返回文本列中的数字字符串，而在外部查询中尝试将这些字符串转换为数字数据类型，但是令人惊讶的是，您收到有关尝试转换非数字字符串的错误转换为数字数据类型。这是因为优化器可以自由地以自己喜欢的任何方式重组查询，并且可以在过滤包含数字的字符串之前将其转换为数字。临时表虽然需要两个语句（一个语句插入数据，第二个语句联接到该数据），但不会出现此问题，因为查询是截然不同的，并且在使用数据之前，数据确实如预期的那样物化。

• A temp table persists over many queries (for the lifetime of the client connection, or until explicitly dropped) but a CTE only "exists" for one query.
• A CTE, while logically a "single" table, is likely to have its data generated multiple times if used multiple times in a query. A temp table's data would simply be read as any other "real" table. In the above example, the Avg(SalesTotal) calculation, in versions of SQL Server through at least 2012, will involve a completely separate operation of performing the SalesTotals aggregate a second time. While it is possible for the engine to materialize the results of the CTE, so far SQL Server has not done this. It is notable that other DBMSes such as Oracle may materialize the results of a CTE. In any case, you should be aware that this double-querying can have (of course!) serious performance implications.
• A temp table has column statistics automatically generated for it and this can aid the query optimizer in choosing better execution plans. A CTE's "final" rowset has no statistics--the statistics of the underlying tables are used.
• A temp table can be added to incrementally or have rows deleted from it by multiple or repeated statements. It can be updated.
• A temp table can be modified to add or remove columns or change data types.
• A temp table can have clustered and non-clustered indexes and constraints.
• You cannot use a temp table in any way inside a user-defined function.
• A CTE, while appearing to logically segregate parts of a query, does no such thing. CTEs are perfect candidates for predicate push-down, elimination if it is determined they do not affect the final rowset (or some of their tables or joins eliminated), or they may be subject to unexpected expression evaluation order. For example, in a CTE you might return only the numeric strings from a text column, and in the outer query try to convert these strings to a numeric data type, but to your surprise you get an error about attempting to convert non-number strings to a numeric data type. That is because the optimizer is free to reorganize your query in any way it pleases, and may do the conversion to numeric before the filter for number-containing strings. A temp table, while requiring two statements (one to insert the data, and a second to join to that data) would not have this problem as the queries are distinct and the data truly "materialized" as expected before using it.

最后，CTE可以执行临时表无法执行的操作：它可以递归。在Oracle中，这是通过 CONNECT BY 表示的，而在SQL Server中，这是通过CTE中的 UNION ALL SELECT 完成的。

Last, a CTE can do something a temp table cannot: it can be recursive. In Oracle this is expressed through CONNECT BY, and in SQL Server it is done with a UNION ALL SELECT inside the CTE that is allowed to refer to the CTE's own alias.

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

Be careful with CTEs--they are a great abstraction, but are nothing more than that, and you can run into serious trouble with them. Generating a million rows can be done with a recursive CTE one row at a time, but it's the WORST possible way by like a hundred times over or more.

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

There is another special kind of temp table in SQL Server 2005 and up called a "table variable" that is very much like a temp table (and kept in tempdb exactly the same), with a few notable exceptions:

• 它只持续 batch 的持续时间，不持续 connection 。


• 您可以在用户定义的函数内使用表变量。某些类型的UDF需要一个。


• 它只能声明内联约束（例如主键或唯一性），并且虽然可以更新/插入/删除行，但是其架构不能在声明后以任何方式进行修改，因此不会添加/删除列，更改数据类型或添加索引。


• 它不收集统计信息。


• 可以在SQL Server 2008及更高版本中将其作为参数（表值参数）传递。

• It only lasts the duration of the batch, not the connection.
• You can use a table variable inside a user-defined function. Some types of UDFs require one.
• It can only have inline constraints declared (such as primary keys or uniqueness), and while it can have rows updated/inserted/deleted, its schema cannot be modified after declaration in any way, so no adding/removing columns, changing data types, or adding indexes.
• It does not collect statistics.
• It can be passed as a parameter (table-valued parameter) in SQL Server 2008 and up.

这篇关于是用#TEMP表的替代品吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！