According to this post, the correct way of generating a random value in SQL Server is:
ABS(CHECKSUM(NewId())) % 14 -- Returns a value between 0 and 13
However, when using this expression in a case statement, such as:
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
The outputtet RandomString column contains some NULLs, as can be seen in this SQL fiddle. I have found that I can wrap the randomizing expression in a CTE to avoid NULLs in the output, but I still wonder why the code above returns NULLs?
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
I have tried to generate the random number using a slightly different method, but the result is the same:
CAST(RAND(CHECKSUM(NEWID())) * 4 AS INT) -- Returns a value between 0 and 3
This seems to be a problem on SQL Server 2014, I have not tested it on other versions.
Aucun commentaire:
Enregistrer un commentaire