主页 / dba / 问题 / 30345
Accepted
JM Hicks
Asked: 2012-12-14 01:53:44 +0800 CST

连接虚拟表中的 NEWID() 导致意外的交叉应用行为

  • 772

我的实际工作查询是内部联接，但这个带有交叉联接的简单示例似乎几乎总是重现问题。

SELECT *
FROM (
    SELECT 1 UNION ALL
    SELECT 2
) AA ( A )
CROSS JOIN (
    SELECT NEWID() TEST_ID
) BB ( B )

通过我的内部连接，我有很多行，我使用 NEWID() 函数为每行添加了一个 GUID，对于 10 个这样的行中的大约 9 个，与 2 行虚拟表的乘法产生了预期的结果，只有 2 个副本相同的 GUID，而十分之一会产生不同的结果。这至少可以说是出乎意料的，让我很难在我的测试数据生成脚本中找到这个错误。

如果您使用非确定性 getdate 和 sysdatetime 函数查看以下查询，您将看不到这一点，我无论如何也不会看到 - 我总是在两个最终结果行中看到相同的 datetime 值。

SELECT *
FROM (
    SELECT 1 UNION ALL
    SELECT 2
) AA ( A )
CROSS JOIN (
    SELECT GETDATE() TEST_ID
) BB ( B )

SELECT *
FROM (
    SELECT 1 UNION ALL
    SELECT 2
) AA ( A )
CROSS JOIN (
    SELECT SYSDATETIME() TEST_ID
) BB ( B )

我目前正在使用 SQL Server 2008，我现在的工作是在完成我的随机数据生成脚本之前将带有 GUID 的行加载到表变量中。一旦我将它们作为表而不是虚拟表中的值，问题就消失了。

我有一个解决方法，但我正在寻找没有实际表或表变量的解决方法。

在写这篇文章时，我尝试了这些可能性但没有成功：1）将 newid() 放入嵌套的虚拟表中：

SELECT *
FROM (
    SELECT 1 UNION ALL
    SELECT 2
) AA ( A )
CROSS JOIN (
    SELECT TEST_ID
    FROM (
        SELECT NEWID() TEST_ID
    ) TT
) BB ( B )

2) 将 newid() 包装在强制转换表达式中，例如：

SELECT CAST(NEWID() AS VARCHAR(100)) TEST_ID

3) 颠倒连接表达式中虚拟表的出现顺序

SELECT *
FROM (
    SELECT NEWID() TEST_ID
) BB ( B )
CROSS JOIN (
    SELECT 1 UNION ALL
    SELECT 2
) AA ( A )

4）使用不相关的交叉应用

SELECT *
FROM (
    SELECT NEWID() TEST_ID
) BB ( B )
CROSS APPLY (
    SELECT 1 UNION ALL
    SELECT 2
) AA ( A )

就在最终发布这个问题之前，现在我成功地尝试了这个，似乎相关的交叉应用：

SELECT *
FROM (
    SELECT NEWID() TEST_ID
) BB ( B )
CROSS APPLY (
    SELECT A
    FROM (
        SELECT 1 UNION ALL
        SELECT 2
    ) TT ( A )
    WHERE BB.B IS NOT NULL
) AA ( A )

有人有其他更优雅、更简单的解决方法吗？如果我不需要的话，我真的不想使用交叉应用或相关来进行简单的行乘法。

sql-server sql-server-2008

1 个回答

  1. Best Answer

    此行为是设计使然，如本 Connect 错误报告中详细说明的那样。为方便起见，下面复制了最相关的 Microsoft 回复（以防链接在某个时候失效）：

    Microsoft 于 2008 年 7 月 7 日上午 9:27 发布

    关闭循环。. . 我已经与开发团队讨论过这个问题。最终我们决定不改变当前的行为，原因如下：

    1. 优化器不保证标量函数的执行时间或次数。这是一个由来已久的原则。这是基本的“回旋余地”，它允许优化器有足够的自由度来显着改进查询计划的执行。

    2. 这种“每行一次的行为”并不是一个新问题，尽管它没有被广泛讨论。我们在 Yukon 版本中开始调整它的行为。但是，在所有情况下，很难准确地确定它的确切含义！例如，它是否适用于“在途中”计算到最终结果的临时行？- 在这种情况下，它显然取决于选择的计划。还是仅适用于最终将出现在完成结果中的行？- 这里有一个讨厌的递归，我相信你会同意的！

    3. 正如我之前提到的，我们默认为“优化性能”——这适用于 99% 的情况。1% 的可能会改变结果的情况很容易发现——诸如 NEWID 之类的副作用“功能”——并且很容易“修复”（因此交易性能）。这种“优化性能”的默认设置由来已久，并且被接受。（是的，这不是编译器为传统编程语言选择的立场，但就这样吧）。

    所以，我们的建议是：

    1. 避免依赖非保证时间和执行次数语义。
    2. 避免在表表达式中使用 NEWID()。
    3. 使用 OPTION 强制执行特定行为（交易性能）

    希望这个解释有助于澄清我们将这个错误关闭为“无法修复”的原因。

    GETDATEand函数确实是不确定的SYSDATETIME，但它们被视为特定查询的运行时常量。从广义上讲，这意味着函数的值在查询执行开始时被缓存，结果被重新用于查询中的所有引用。

    问题中的任何“解决方法”都不安全。无法保证下次编译计划时、下次应用服务包或累积更新时行为不会改变……或其他原因。

    唯一安全的解决方案是使用某种临时对象——例如变量、表或多语句函数。使用基于观察在今天看来可行的解决方法是在未来体验意外行为的好方法，通常以周日凌晨 3 点的寻呼警报的形式出现。

    • 21

相关问题