如何在单SQL查询中生成并重复NEWID()值？

问题场景 #

需要生成测试数据：先生成N1个唯一的UNIQUEIDENTIFIER值，再通过CROSS APPLY将每个值重复N2次。原SQL写法如下：

SELECT *
FROM (
    SELECT
        S1.value,
        NEWID() AS Id
    FROM GENERATE_SERIES(1, 3) S1
) S1
CROSS APPLY (
    SELECT
        S2.value,
        S1.Id -- 期望重复同一ID值多次
    FROM GENERATE_SERIES(1, 2) S2
) S2
ORDER BY S1.Value, S2.Value

现象与原因 #

期望每个初始生成的Id被重复多次，但实际每次CROSS APPLY关联时都会生成新的Id。这是因为NEWID()属于非确定性函数，SQL优化器的设计会导致这类函数的执行时机不固定——即使放在子查询中，优化器可能会选择在关联阶段重新执行它，而非保留子查询中生成的结果。

单语句解决方案 #

以下几种单语句写法可以实现需求，核心是确保NEWID()仅在生成基础行时执行一次，后续关联复用该值：

方案1：将NEWID()绑定到基础行的独立APPLY中

SELECT
    S1.value AS BaseValue,
    S2.value AS RepeatValue,
    GuidGen.Id
FROM GENERATE_SERIES(1, 3) S1
CROSS APPLY (SELECT NEWID() AS Id) AS GuidGen -- 每个基础行仅生成一次GUID
CROSS APPLY GENERATE_SERIES(1, 2) S2
ORDER BY S1.value, S2.value;

方案2：用OFFSET强制物化子查询

通过OFFSET 0 ROWS提示优化器物化子查询，固定NEWID()的结果：

SELECT *
FROM (
    SELECT
        S1.value,
        NEWID() AS Id
    FROM GENERATE_SERIES(1, 3) S1
    OFFSET 0 ROWS -- 强制子查询执行并保留结果
) S1
CROSS APPLY (
    SELECT
        S2.value,
        S1.Id
    FROM GENERATE_SERIES(1, 2) S2
) S2
ORDER BY S1.Value, S2.Value;

方案3：使用CTE结合物化提示（部分SQL方言支持）

如果CTE默认不被物化，可添加提示强制：

WITH BaseIds AS (
    SELECT
        S1.value,
        NEWID() AS Id
    FROM GENERATE_SERIES(1, 3) S1
    OPTION (MAXDOP 1) -- 部分环境下可强制物化，视数据库方言调整
)
SELECT
    B.value AS BaseValue,
    S2.value AS RepeatValue,
    B.Id
FROM BaseIds B
CROSS APPLY GENERATE_SERIES(1, 2) S2
ORDER BY B.value, S2.value;

总结 #

非确定性函数的执行时机由优化器决定，通过调整写法将函数的执行绑定到基础行生成阶段，即可实现GUID的重复复用。如果上述单语句方案在你的数据库环境中不生效，临时表仍是最稳妥的替代方案。

内容的提问来源于stack exchange，提问作者T N