Início / dba / Perguntas / 80558
Accepted
usr
Asked: 2014-10-19 07:05:42 +0800 CST

Resultados de consulta estranhos para DELETE com ROW_NUMBER

  • 772

Na DELETEinstrução a seguir, tento excluir todas as linhas, exceto a primeira ordenada por alguns critérios. (A consulta real faz mais sentido, isso é apenas uma reprodução. Todo o sys.objectsmaterial é apenas para gerar dados de teste.)

Observe o filtro r <> 1. No entanto, a OUTPUTcláusula gera linhas excluídas com r = 1. Como isso pode ser?

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

Resultados da consulta:

insira a descrição da imagem aqui

(O terceiro conjunto de resultados está completo - apenas uma linha.)

Observe que todas as linhas, exceto a primeira, foram excluídas. A ordem de numeração da coluna rparece não corresponder ao que foi solicitado. E há uma linha com r = 1.

Este é o SQL Server 2014 CU3 com o sinalizador de rastreamento 4199 ativado.

sql-server sql-server-2014

2 respostas

  1. Best Answer

    Este é o comportamento esperado no momento

    a função é avaliada no fluxo DELETE.

    Na verdade, ele se comporta assim (pseudocódigo)

    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)

    Embora este seja o comportamento esperado atualmente definido, não é realmente razoável e eles dizem

    A longo prazo, precisamos corrigir o comportamento da cláusula OUTPUT para corresponder ao padrão ANSI SQL, o que resultará na alteração dos resultados. Portanto, veremos a semântica correta para uma versão futura do SQL Server, pois pode haver aplicativos que dependem do comportamento atual.

    Não testei no SQL Server 2014, mas no 2012 o plano é o seguinte.

    insira a descrição da imagem aqui

    Após o operador delete (à esquerda), os valores das colunas das linhas excluídas são classificados de volta em object_idordem e o row_number reaplicado.

    Parece que o mesmo está acontecendo no seu caso a partir dos resultados. (as tabelas temporárias têm um id negativo e são classificadas primeiro antes das sysrscolsquais têm um baixo positivo object_idde 3).

    Assim como a semântica duvidosa do resultado, a segunda classificação por object_idnão parece estritamente necessária neste plano, pois parece provável que eles já estejam classificados nessa ordem em qualquer caso.

    Em relação às soluções alternativas para este caso específico, alterar a cláusula de saída para OUTPUT Deleted.name, 1 + Deleted.r AS rfuncionaria.

    Para cláusulas mais complicadas WHERE, acho que você precisaria de um passe para calcular o row_number e, em seguida, uma junção. por exemplo

    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;

    insira a descrição da imagem aqui

    Resultados

    insira a descrição da imagem aqui

    • 12

  2. Você realmente precisa de ROW_NUMBER e OUTPUT? Que tal um bom DELETE baseado em conjunto, por exemplo, algo assim:

    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

relate perguntas