SQL Server中CHOOSE搭配RAND随机返回null的原因咨询

问题原因 #

该现象由SQL Server中RAND()函数的计算特性与CHOOSE函数的底层实现共同导致：

• 未指定固定种子的RAND()函数每被调用一次就会生成新的随机值，并非单次查询内写一次就只会计算一次。
• CHOOSE是语法糖，底层会被查询优化器展开为等价的CASE语句，你用到的CHOOSE(FLOOR(RAND() * 4) + 1, 5, 15, 16, 17)实际等价于：

  CASE
      WHEN FLOOR(RAND() * 4) + 1 = 1 THEN 5
      WHEN FLOOR(RAND() * 4) + 1 = 2 THEN 15
      WHEN FLOOR(RAND() * 4) + 1 = 3 THEN 16
      WHEN FLOOR(RAND() * 4) + 1 = 4 THEN 17
      ELSE NULL
  END
  

每次判断CASE的一个WHEN条件时，都会重新执行一次RAND()计算索引值，很有可能出现所有WHEN条件都匹配失败的情况，最终走ELSE NULL分支返回空值。

而子查询写法中，FLOOR(RAND() * 4) + 1在子查询执行阶段仅会计算一次，生成固定的1-4之间的整数值，外层CHOOSE用这个固定值做匹配，必然能命中四个返回值中的一个，因此不会出现返回空值的情况。

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