SQL RAND() gotcha

I was using RAND() in an INSERT statement, using values SELECTed from a different table. I assumed that RAND() would be executed for each row from the SELECT statement, but instead I discovered it is only executed once per query. Simple gotcha.

INSERT		TableName (ColumnA, ColumnB, ColumnC)
-- RAND() is executed once per query, so all inserted rows will have the same value
SELECT		ValueA, ValueB, RAND() 
FROM		AnotherTable

Depending on what you’re trying to achieve, using RAND(seed) with an integer seed value taken from the values being inserted forces the function to evaluate once per row with the different seed, resulting in different values. However, since RAND is pseudo-random, using the same seed value will result in the same output.

Something to add?

This site uses Akismet to reduce spam. Learn how your comment data is processed.