Início / dba / Perguntas / 340504
Accepted
T N
Asked: 2024-06-26 01:10:30 +0800 CST

"ROW_NUMBER() OVER(ORDER BY xml.node)" está bem definido?

  • 772

(É mais uma questão de documentação do que de comportamento . Foi migrado do Stack Overflow como foi sugerido lá.)

Ao pesquisar a resposta para outra pergunta que exigia a extração de nós XML na ordem original dos elementos, me deparei com várias respostas ( aqui , aqui e aqui ) que usavam expressões do formato ROW_NUMBER() OVER (ORDER BY xml.node)e afirmavam ou implicavam que os valores de número de linha atribuídos seriam atribuído na ordem do documento XML.

No entanto, não consigo encontrar nenhum lugar que defina o comportamento do ORDER BY xml.node. Embora pareça permitido na OVER()cláusula, a documentação não menciona especificamente os nós XML.

Por exemplo, dado:

DECLARE @xml XML = '<root>
<node>One</node>
<node>Two</node>
<node>Three</node>
<node>Four</node>
</root>'

SELECT
    ROW_NUMBER() OVER(ORDER BY xml.node) AS rn,
    xml.node.value('./text()[1]', 'varchar(255)') AS value
FROM @xml.nodes('*/node') xml(node)
ORDER BY
    ROW_NUMBER() OVER(ORDER BY xml.node)

Os seguintes resultados são retornados:

rn | value
----------
1  | One
2  | Two
3  | Three
4  | Four

Pergunta: Esses resultados são garantidos em algum lugar da documentação? Isso é aceito como comportamento garantido, mas não documentado? Ou este é outro caso como ORDER BY (SELECT NULL)esse que parece funcionar de forma anedótica para pequenos conjuntos de dados de origem aparentemente pré-ordenados, mas pode eventualmente falhar quando ampliado? A razão pela qual estou perguntando é que prefiro não recomendar o uso de uma técnica cujo comportamento e confiabilidade não são suportados pela documentação.

Curiosamente, embora um nó XML seja permitido em um window ORDER BY, ele não é permitido em um SELECT ... ORDER BY. Quando usado em uma cláusula select order-by comum, ORDER BY xml.nodegera o seguinte erro:

Msg 493 Nível 16 Estado 1 Linha 7

A coluna 'node' que foi retornada do método nodes() não pode ser usada diretamente. Ele só pode ser usado com um dos quatro métodos de tipo de dados XML, exist(), nodes(), query() e value(), ou em verificações IS NULL e IS NOT NULL.

A mensagem de erro acima não lista a função de janela OVER(ORDER BY ...)como um uso permitido.

Veja este db<>fiddle .

sql-server

1 respostas

  1. Best Answer

    Não, não está documentado e, portanto, não é garantido.

    Na prática, provavelmente é garantido conforme descrevi em uma resposta do Stack Overflow (reproduzida abaixo), mas isso não atende ao seu desejo declarado de documentação oficial.

    Você pode pedir que eles documentem isso abrindo um problema de documentação .

    Eu pessoalmente usaria isso porque a evidência é aceitável para mim, mas não irei indenizá-lo por suas perdas se você fizer o mesmo.

    Minha resposta do Stack Overflow

    Não há como ver isso explicitamente em um plano de execução, mas a idcoluna retornada pelo nodes()método é varbinary(900) OrdPath , que encapsula o documento xml original order .

    A solução oferecida por Mikael Eriksson para a questão relacionada O nodes()método mantém a ordem dos documentos? depende do OrdPath para fornecer uma ORDER BYcláusula necessária para determinar como os valores de identidade são atribuídos ao arquivo INSERT.

    Segue um uso um pouco mais compacto:

    CREATE TABLE #T 
(
    ID integer IDENTITY, 
    Fruit nvarchar(10) NOT NULL
);

DECLARE @xml xml = 
    N'
    <Fruits>
      <Apple />
      <Banana />
      <Orange />
      <Pear />
    </Fruits>
    ';

INSERT #T 
    (Fruit)
SELECT 
    N.n.value('local-name(.)', 'nvarchar(10)') 
FROM @xml.nodes('/Fruits/*') AS N (n)
ORDER BY
    ROW_NUMBER() OVER (ORDER BY N.n);

SELECT 
    T.ID, 
    T.Fruit 
FROM #T AS T
ORDER BY
    T.ID;

    banco de dados<> violino

    Atualmente, usar o OrdPath desta forma não está documentado, mas a técnica é sólida em princípio:

    1. O OrdPath reflete a ordem dos documentos.
    2. Os ROW_NUMBERvalores de sequência computados ordenados por OrdPath *.
    3. A ORDER BYcláusula usa a sequência numérica da linha.
    4. Os valores de identidade são atribuídos às linhas de acordo com o arquivo ORDER BY.

    Para ser claro, isto é válido mesmo se o paralelismo for empregado. Como diz Mikael, o aspecto duvidoso é usar ido ROW_NUMBERjá que idnão está documentado como OrdPath .

    * A ordem não é mostrada nos planos, mas a saída do otimizador usando TF 8607 contém:

    ScaOp_SeqFunc row_number order[CALC:QCOL: XML Reader with XPath filter.id ASC]
    • 10

relate perguntas