主页 / dba / 问题 / 80558
Accepted
usr
Asked: 2014-10-19 07:05:42 +0800 CST

带有 ROW_NUMBER 的 DELETE 的奇怪查询结果

  • 772

在下面的DELETE语句中，我尝试删除除按某些标准排序的第一行之外的所有行。（实际查询更有意义，这只是一个重现。所有的sys.objects东西都只是为了生成测试数据。）

注意过滤器r <> 1。然而，该OUTPUT子句输出已删除的行r = 1。怎么会这样？

USE tempdb
SET XACT_ABORT ON

BEGIN TRAN

    SELECT *
    INTO #o
    FROM sys.objects

    SELECT TOP 2 name FROM #o ORDER BY object_id --debug output

    DELETE k
    OUTPUT Deleted.name, Deleted.r
    FROM (
        SELECT k.*, ROW_NUMBER() OVER (ORDER BY object_id) r
        FROM #o k
    ) k
    WHERE r <> 1 --OUTPUT returns rows with (r = 1)

    SELECT TOP 2 name FROM #o ORDER BY object_id --debug output

ROLLBACK

查询结果：

在此处输入图像描述

（第三个结果集是完整的 - 只有一行。）

请注意，除第一行之外的所有行都已删除。列的编号顺序r似乎与请求的不匹配。并且有一行r = 1。

这是启用了跟踪标志 4199 的 SQL Server 2014 CU3。

sql-server sql-server-2014

2 个回答

  1. Best Answer

    这是目前的预期行为

    该函数在 DELETE 流上进行评估。

    所以它实际上的行为是这样的（伪代码）

    DELETE k
OUTPUT Deleted.name, 
       ROW_NUMBER() OVER (ORDER BY Deleted.object_id) as r
FROM (
    SELECT k.*, ROW_NUMBER() OVER (ORDER BY object_id) r
    FROM #o k
) k
WHERE r <> 1 --OUTPUT returns rows with (r = 1)

    尽管这是当前定义的预期行为，但这并不合理，他们说

    从长远来看，我们需要实际修复 OUTPUT 子句的行为以匹配 ANSI SQL 标准的行为，这将导致结果的变化。因此，我们将查看 SQL Server 未来版本的正确语义，因为可能存在依赖于当前行为的应用程序。

    我尚未在 SQL Server 2014 上进行测试，但在 2012 年，计划如下所示。

    在此处输入图像描述

    在删除运算符（左侧）之后，已删除行的列值object_id重新排序并重新应用 row_number。

    从结果来看，您的情况似乎也发生了同样的情况。（临时表的 id 为负数，并排在最前面，sysrscols其正object_id数较低3）。

    除了结果的可疑语义外，第二个排序依据object_id在这个计划中似乎并不是绝对必要的，因为看起来它们很可能在任何情况下都已经按该顺序排序了。

    关于这种特定情况的解决方法，将输出子句更改为OUTPUT Deleted.name, 1 + Deleted.r AS r可行。

    对于更复杂WHERE的子句，我认为您需要通过来计算 row_number，然后再进行连接。例如

    ALTER TABLE #o
  ADD CONSTRAINT PK PRIMARY KEY (object_id);

WITH k AS
(
 SELECT *, 
        ROW_NUMBER() OVER (ORDER BY object_id) r
FROM #o
)
MERGE k AS k1
using k AS k2
ON k1.object_id = k2.object_id
WHEN matched AND k2.r <> 1 THEN
  DELETE
OUTPUT Deleted.name,
       k2.r;

    在此处输入图像描述

    结果

    在此处输入图像描述

    • 12

  2. 你真的需要 ROW_NUMBER 和 OUTPUT 吗？一个不错的基于集合的 DELETE 怎么样，例如这样的：

    USE tempdb
SET XACT_ABORT ON

BEGIN TRAN

    SELECT *
    INTO #o
    FROM sys.objects

    -- Identify records to keep
    SELECT TOP 2 object_id
    INTO #keep 
    FROM #o 
    ORDER BY object_id

    -- Delete others
    DELETE o
    FROM #o o
    WHERE NOT EXISTS
        (
        SELECT *
        FROM #keep k
        WHERE o.object_id = k.object_id
        )

    RAISERROR( 'There have been no complex ANSI semantics issues during this set-based delete', 10, 1 )

ROLLBACK
    • 2

相关问题