Início / dba / Perguntas / 282790
Accepted
J.D.
Asked: 2021-01-08 18:27:53 +0800 CST

Os resultados da função STRING_SPLIT() são retornados em uma ordem determinística?

  • 772

Eu preciso dividir uma string delimitada por vírgulas, manipulá-la e concatená-la novamente em uma única string mantendo a ordem original dos dados (se possível).

Por exemplo, pegue uma lista de definição de coluna de uma CREATE TABLEinstrução (como uma string) como 'BrentOzarColumn INTEGER, PaulWhiteColumn DATETIME, ErikDarlingColumn VARCHAR(100)'. Eu gostaria de dividir a lista delimitada por vírgulas em um conjunto de resultados, como usar a função interna do SQL Server STRING_SPLIT()assim: SELECT TRIM([Value]) AS CoolDataPeople FROM STRING_SPLIT('BrentOzarColumn INTEGER, PaulWhiteColumn DATETIME, ErikDarlingColumn VARCHAR(100)', ',').

Sem especificar uma ORDER BYcláusula, isso repetidamente produz (por coincidência?) os seguintes resultados que parecem ser ordenados pela mesma ordenação que estão na string:

Resultados

Depois de ter o resultado definido acima, quero aplicar algumas manipulações de string adicionais a cada linha (como anexar algum texto constante) e, em seguida, concatenar cada linha de volta com uma função como STRING_AGG()(adeus dias de STUFF ... FOR XML PATH:) na mesma ordem que o corda original. Então, um exemplo do meu resultado final poderia ser 'BrentOzarColumn INTEGER SQLROX, PaulWhiteColumn DATETIME SQLROX, ErikDarlingColumn VARCHAR(100) SQLROX'.

Em última análise, minha pergunta é: Os resultados da STRING_SPLIT()função são retornados em uma ordem determinística? Eu sei que sem uma ORDER BYcláusula, a ordenação não é garantida ao selecionar um conjunto de dados como a Tableou View, mas queria saber se há uma diferença com as funções?

Enquanto digito isso, tenho um palpite de que a resposta é não, a ordenação não é determinística, portanto, não tenho garantia da ordem dos resultados. Além disso, estou apostando que há um possível não determinismo adicional adicionado para cada função que executo em cima dos resultados, especialmente quando os combino novamente com STRING_AGG(). (Independentemente da resposta, agradeço sua ajuda e todos vocês são pessoas legais com dados. ;)

sql-server sql-server-2017

1 respostas

  1. Best Answer

    Não, eles não são retornados em uma ordem determinística.

    Embora seja improvável que você os veja retornados em uma ordem diferente, isso não torna o comportamento atual determinístico ou confiável. Truques como aplicar ROW_NUMBER() OVER (ORDER BY (SELECT 1))à saída em um local intermediário, como um CTE, também não são garantidos para funcionar.

    É um dos recursos notavelmente ausentes do STRING_SPLIT(), sobre o qual escrevi no blog:

    E Andy Mallon também:

    Vote e comente aqui:

    (Este item pede especificamente que uma coluna adicional seja retornada para indicar a posição dentro da string original, mas, devido a problemas de compatibilidade com versões anteriores, provavelmente precisaria ser entregue por meio de uma nova função, semelhante a como CONCAT_WSsurgiu.)

    Como um aparte , a documentação originalmente dizia:

    A ordem de classificação das linhas de saída corresponde à ordem das substrings na string de entrada.

    Isso foi deliberadamente alterado neste commit para remover qualquer noção de que a ordem de retorno é prometida. Agora a documentação diz:

    As linhas de saída podem estar em qualquer ordem. Não é garantido que a ordem corresponda à ordem das substrings na string de entrada.

    Por que eles precisariam remover essa promessa?

    Não estou familiarizado com a implementação interna, mas acredito que a versão original da documentação foi escrita por observadores, não pelos autores da função. Essa declaração provavelmente foi escrita dessa maneira porque foi o comportamento que eles observaram quando usaram a função. Muitos de nós fazemos a mesma coisa quando dizemos às pessoas que não precisamos ORDER BYao selecionar linhas de uma tabela agrupada: "elas sempre sairão nesta ordem". Bastante confiável, até que o otimizador escolha um índice diferente.

    Meu palpite é que eles estão cobrindo suas bundas para comportamento futuro. Pense em todas as mudanças que eles fizeram no funcionamento das funções e também nas mudanças contínuas no otimizador.

    Atualmente, com o caso simples:

    SELECT * FROM STRING_SPLIT('cow,dog,dinosaur','');

    Não há muita oportunidade ou razão para que isso não seja sequencial. Mas e quando você está OUTER APPLYem uma nvarchar(max)coluna entre duas enormes tabelas particionadas e os dados começam a ser processados ​​em modo de lote e/ou em paralelo? Eles teriam que escrever lógica adicional no código para garantir que toda essa saída voltasse na ordem correta? Valeria a pena? Eles teriam que revisitá-lo para cada alteração futura de processamento/otimizador de função para manter essa promessa?

    Que mudanças eles estão fazendo?

    A documentação foi atualizada recentemente para mostrar um novo argumento para a função, enable_ordinal, disponível no Banco de Dados SQL do Azure e em breve no SQL Server 2022 :

    enable_ordinal

    Uma expressão int ou bit que serve como um sinalizador para habilitar ou desabilitar a coluna de saída ordinal. Um valor de 1 habilita a coluna ordinal. Se enable_ordinal for omitido, NULL ou tiver um valor de 0, a coluna ordinal será desabilitada.

    Observação

    O argumento enable_ordinal e a coluna de saída ordinal são atualmente suportados apenas no Banco de Dados SQL do Azure, Instância Gerenciada de SQL do Azure e Azure Synapse Analytics (somente pool de SQL sem servidor).

    Para ser claro, adicionar este argumento apenas adiciona uma ordinalcoluna à saída indicando a sequência de valuedentro da string original; ainda não garante que a saída será ordenada pela ordinalcoluna . Para isso, você ainda precisa adicionar ORDER BYà saída.

    • 11

relate perguntas