Gere e repita valores NEWID() em uma única consulta

Estou tentando gerar e duplicar vários UNIQUEIDENTIFIERvalores usando NEWID()como dados de teste. A seguir, minha tentativa é primeiro gerar valores N1 e, em seguida, usar a CROSS APPLYpara duplicar esses valores N2 vezes cada:

SELECT *
FROM (
    SELECT
        S1.value,
        NEWID() AS Id
    FROM GENERATE_SERIES(1, 3) S1
) S1
CROSS APPLY (
    SELECT
        S2.value,
        S1.Id -- Trying to repeat the same ID value multiple times
    FROM GENERATE_SERIES(1, 2) S2
) S2
ORDER BY S1.Value, S2.Value

Resultados desejados:

Resultados reais:

Entendo que NEWID()isso gerará valores exclusivos para cada linha onde for usado, mas teria pensado que, quando usados ​​em uma subconsulta, os valores seriam gerados nessa subconsulta em vez de serem gerados no nível mais externo. Não consigo encontrar uma explicação clara desse comportamento na documentação do NEWID ou em outro lugar.

Tentei inserir cálculos intermediários usando CAST(), TOUPPER()e REPLACE(), que achei que poderiam capturar e solidificar os NEWID()valores. Também tento encerrar a primeira parte em um CTE. Ainda não consigo repetir os valores gerados.

Posso fazer isso em várias etapas usando uma tabela #temp ou uma variável @table, mas queria saber se existe uma maneira de fazer isso em uma única instrução.

banco de dados<> violino

Aqui está outro caso semelhante:

-- Another similar case
WITH CTE AS (
    SELECT NEWID() AS Id -- Is this one value or many?
)
SELECT *
FROM ( VALUES (1), (2), (3) ) ID(Id)
CROSS JOIN CTE

Seguir:

Eu descobri uma pergunta semelhante no dba stackexchange que me apontou para um relatório e discussão de bug do Microsoft Connect de 15 anos , que terminou com o relatório de bug sendo fechado como não será corrigido, funções conforme projetado. A justificativa é que "o otimizador não garante o tempo ou o número de execuções de funções escalares" e "é muito difícil definir com precisão ... o que significa o comportamento uma vez por linha ".

Essa mesma postagem teve um comentário tardio que encontrou uma solução alternativa usando uma combinação de GROUP BYe FULL OUTER JOINpara persuadir o comportamento desejado, mas comentários posteriores sugerem que esse ainda era um comportamento indefinido.

Também encontrei alguma documentação on-line de livros antigos de 2005 que inclui uma prática recomendada: "Limitar o uso de funções não determinísticas às listas de seleção mais externas". Não consegui encontrar o mesmo na documentação atual, mas talvez não estivesse procurando no lugar certo.

Portanto, embora a resposta de ValNik abaixo sugira uma solução alternativa envolvendo GROUP BY, acho que isso também é construído em terreno instável. (A resposta de ValNik, na verdade, começa com um comentário “não faz sentido”.)

Também descobri que adicionar uma IS NOT NULLverificação também estimula o resultado desejado ( violino ). Novamente, este é provavelmente um comportamento indefinido que parece funcionar - é melhor evitar.

Resumindo, provavelmente é melhor seguir a antiga prática "Limitar o uso de funções não determinísticas às listas de seleção mais externas", o que significa selecionar uma tabela temporária ou variável de tabela, conforme recomendado por Thom A e documentado como resposta.