sql-server - CASE 语句中随机生成的值返回 NULL

Question

根据this post ，在SQL Server中生成随机值的正确方法是:

ABS(CHECKSUM(NewId())) % 14   -- Returns a value between 0 and 13

但是，在 case 语句中使用此表达式时，例如:

SELECT
    CASE ABS(CHECKSUM(NEWID())) % 4 
        WHEN 0 THEN 'String A' 
        WHEN 1 THEN 'String B' 
        WHEN 2 THEN 'String C' 
        WHEN 3 THEN 'String D'
    END AS RandomString -- Returns String A, B, C, D and NULLs.
FROM sys.all_objects

outputtet RandomString 列包含一些 NULL，如 SQL fiddle 中所示。 。我发现我可以将随机化表达式包装在 CTE 中以避免输出中出现 NULL，但我仍然想知道为什么上面的代码返回 NULL？

WITH RandomNumber AS (
    SELECT ABS(CHECKSUM(NEWID())) % 4 AS N FROM sys.all_objects
)
SELECT TOP 100
    CASE N
        WHEN 0 THEN 'String A' 
        WHEN 1 THEN 'String B' 
        WHEN 2 THEN 'String C' 
        WHEN 3 THEN 'String D'
    END AS RandomString -- Does not return any NULLs. Only String A, B, C and D.
FROM RandomNumber

我尝试使用稍微不同的方法生成随机数，但结果是相同的:

CAST(RAND(CHECKSUM(NEWID())) * 4 AS INT)  -- Returns a value between 0 and 3

这似乎是SQL Server 2014上的问题，我没有在其他版本上测试过。

Answer 1

它会生成 NULL，因为无法保证特定表达式将被计算多少次。

您希望 SQL Server 执行以下操作:

let x = GenerateRandomNumber()
if x = 1 then 'String 1'
if x = 2 then 'String 2'
if x = 3 then 'String 3'
if x = 4 then 'String 4'

(其中 GenerateRandomNumber() 为 ABS(CHECKSUM(NEWID())) % 4);但 SQL Server 实际上做了的是:

if GenerateRandomNumber() = 1 then 'String 1'
if GenerateRandomNumber() = 2 then 'String 2'
if GenerateRandomNumber() = 3 then 'String 3'
if GenerateRandomNumber() = 4 then 'String 4'

因此，如果您碰巧为某个特定比较操作选择了正确的随机数，您只会得到非NULL 结果。

我认为即使有了 CTE，也不能保证 SQL Server 永远不会生成类似于上面第二个代码块的内容。如果您想要一个稳定、生成一次的随机数，您需要将该值存储到某个地方(例如在表变量或临时表中)。

<小时/>

我关注保证的原因是您不希望最终根据当前观察到的行为编写代码。当 SQL Server 2008 停止在 View 中“排序”结果时，报告了很多“问题”，我们正在使用 TOP 100 PERCENT ... ORDER BY 技巧 - 碰巧起作用的东西(主要是)在 2005 年及更早版本上，但不再这样做。

同样，如果有人向我询问返回数字 5 的表达式，我可以向他们提供表达式 DATEPART(day,GETUTCDATE()) 并让他们运行在接下来的 8 小时内，对尽可能多的行进行尽可能多的查询 - 但这并不意味着我会推荐它作为他们问题的解决方案。

而且，我们知道 SQL Server 在某些方面的决策涉及 evaluation order可能会令人惊讶。