It's not often that we (as programmers) need random numbers, especially from a database. When you are testing your queries for performance, it's best to use a large table to do it. Sometimes you just don't have access to a large table to test with, so you may think about creating many rows in your existing table.

SQL Server has a rand() function that will return a random (fractional) number between 0 and 1.
For example:

The problem with the rand function occurs when you use set based operations.

RandomNumber
----------------------
0.920057583532051
0.920057583532051
0.920057583532051

Not very random, is it? I mean, the number is random, but it's also the same for each row. The purpose of this blog is to demonstrate how to get a random value in set based operations.

There is an interesting technique that you can use to accomplish this. There is a NewId() function in SQL Server that returns a GUID, for example: '94344EE4-5D7A-45EB-9EBC-7A596B7F90F3'. NewId does work well for set based operations, for example:

RandomNumber           GUID
---------------------- ------------------------------------
0.130542187318667      D12D6274-CB93-4CDB-B848-9F0FA8B88614
0.130542187318667      C708217F-5204-4284-B76B-A55D87BEFBC0
0.130542187318667      B42D7973-B3E2-420F-8D4D-DC72442D0824

Notice that the 'Random Number' column is the same for each row, but the GUID column is different. We can use this interesting fact to generate random numbers by combining this with another function available in SQL Server. The CHECKSUM function will return an integer hash value based on its argument. In this case, we can pass in a GUID, and checksum will return an integer.

RandomNumber           GUID                                 RandomInteger
---------------------- ------------------------------------ --------------
0.152440960483059      CDEB9F5D-E8A2-4FC7-A5A4-0E0A9CCF9786     84,364,212
0.152440960483059      9ABCEC8F-6C0C-4CEF-BA16-E8F0B50FB399 -1,317,220,961
0.152440960483059      6BD23217-F3FF-4F19-AA7F-C127CA7A4763    976,389,102

Before we continue, let's take a look at the output, because there are some interesting observations that we need to consider before continuing. RandomInteger can be positive or negative because it's limited to the range of an integer, so the values must fall between -2,147,483,648 and 2,147,483,647.

Most of the time, we want a random number within a certain range of numbers. In most languages, we simply multiply the result of the Rand() function to get this number. Since our RandomInteger is already a whole number, we really can't do this. However, we could use the mod operator to guarantee a range of numbers. Mod is the remainder of a division operation, so if we mod a number by 10, we are guaranteed to get a number between -9 and +9. Unfortunately, this is a little misleading because there are 19 possible numbers we can get for this. So, to make sure we get a range to 10 numbers, we need to take the absolute value of the number, and then mod 10. Like this:

RandomNumber           GUID                                 RandomInteger
---------------------- ------------------------------------ -------------
0.490769895131745      8237A3F3-98C6-4B31-8F4B-8A80ECC8FBFF 1
0.490769895131745      C231EB65-FA81-4536-B909-44FEC91953D9 9
0.490769895131745      F0AD28C5-8AE4-46A1-B8AC-D8C9F90A5747 4

Now, we are guaranteed to get a Random number between 0 and 9. Suppose you want to get a random number between 10 and 15. The range of numbers is 6 (10,11,12,13,14,15). The mod value for this needs to be 6, to get a number in the range of 0 to 5. Then, we add 10 to the result to get a number in the range of 10 to 15.

If you want to generate a random number between -5 and 5, don’t be tempted to remove the absolute value function, because you will get numbers that appears to be random, but are not 'as random' as they should be.

In my database, I have a numbers table with 1,000,000 rows. When I run this code:

RandomNumber NumberCount
------------ -----------
-5           83539
-4           83443
-3           83372
-2           82931
-1           82950
0            166254
1            83207
2            83635
3            83741
4            83417
5            83511

Notice that 0 has double the number of occurrences. Instead, we should write it like this:

RandomNumber NumberCount
------------ -----------
-5           90889
-4           90794
-3           91365
-2           90476
-1           91104
0            90730
1            90895
2            90815
3            90762
4            91133
5            91037

Notice now that the values are 'more' random than the previous version (where 0 had double the number of occurrences). Since we are talking about random numbers, we wouldn't expect there to be the same number of occurrences for each number, but with the previous version, there were double the number of zero's, which isn't truly random. This version has approximately the same number of zero's as any other number, making it 'more random'.