在SQL Server中使用With子句 [英] Use of With Clause in SQL Server

问题描述

with 子句在SQL Server中如何工作？

How does with clause work in SQL Server? Does it really give me some performance boost or does it just help to make more readable scripts?

何时正确使用它会真的提高性能，还是有助于使脚本更具可读性？在开始使用 with 子句之前，您应该了解什么？

When it is right to use it? What should you know about with clause before you start to use it?

以下是我所使用的示例谈论：

Here's an example of what I'm talking about:

http://www.dotnetspider.com/resources/33984-Use-With-Clause-Sql-Server.aspx

推荐答案

除非使用递归功能，否则 CTE 的性能并不比简单的内联视图更好。

Unless you use recursive abilities, a CTE is not better performance-wise than a simple inline view.

它只是为您节省了一些打字输入。

It just saves you some typing.

优化器可以自由决定是否重新评估它，何时重新使用它以及多数情况下，它决定重新评估：

The optimizer is free to decide whether to reevaluate it or not, when it's being reused, and it most cases it decides to reevaluate:

WITH    q (uuid) AS
        (
        SELECT  NEWID()
        )
SELECT  *
FROM    q
UNION ALL
SELECT  *
FROM    q

将返回两个不同的 NEWID 。

注意Ť其他引擎的行为可能有所不同。

Note that other engines may behave differently.

PostgreSQL 与 SQL Server 不同，它实现了 CTE 。

Oracle 支持特殊提示， / * + MATERIALIZE * / ，告诉优化器是否应实现 CTE 。

Oracle supports a special hint, /*+ MATERIALIZE */, that tells the optimizer whether it should materialize the CTE or not.

这篇关于在SQL Server中使用With子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！