If you use the standard RAND() function call in SQL, it will execute once for a column in a batch, and that same value will be used for every row in your SQL Query. It is possible to get around this problem.
Everyone knows that NEWID() will generate a new guid for every row in the database. If you pass a NEWID() into the CHECKSUM function, it will return an int value (can be positive or negative) between min and max int.
To return a value equivalent to the RAND() function, I simply execute the following:
SELECT ABS(CHECKSUM(NEWID())) / 2147483647.0
This will return a positive value between 0 and 1. Note that the decimal point in the max int figure is important here because it converts it to a floating type – otherwise the result would be truncated and always be zero.
So all you need to do if you want to return a set of random values, say, between 0 and 100 would be to multiply it by 100 and subtract 1. The cast to int simply truncates the result, so that we don’t have decimal places.
SELECT Id, CAST(ABS(CHECKSUM(NEWID())) / 2147483647.0 * 100 - 1 as int) AS RandomNum FROM MyTableContainingLotsOfRows
