Why this query update dbo.product set price= rand(); produces the same random price

Question

The SQL server documentation says that calling the rand function with the same seed produces the same value and that the subsequent calls to rand() will produce values according to the seed first provided.

SELECT RAND(100), RAND(), RAND()

BUT, what about something like:

update dbo.product set price= rand();

This always produced the same value for all rows. What is the "academic" explanation for this behavior?

Related: http://stackoverflow.com/questions/1214748/rand-not-different-for-every-row-in-t-sql-update — Szymon, Sep 17 '14 at 04:42

score 0 · Answer 1 · answered Oct 19 '14 at 12:56

0

Since RAND does not run for each row, you can use ABS(CHECKSUM(NEWID())) to accomplish the random values for each row.

DECLARE @Min INT = 10, @Max INT = 20
UPDATE dbo.product SET price = ABS(CHECKSUM(NEWID())) % (@Max - @Min) + @Min

answered Oct 19 '14 at 12:56

Jason W

13,026
3
31
62

Why this query update dbo.product set price= rand(); produces the same random price

1 Answers1