SQL Server: Random Numbers on a Row by Row basis

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

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: