T/SQL Generate Random Numbers

SQL Server has a built-in function that generates a random number, the RAND() mathematical function.  The RAND math function returns a random float value from 0 through 1.  It can take an optional seed parameter, which is an integer expression (tinyint, smallint or int) that gives the seed or start value.

To use it, you can simply do a simple SELECT, as follows:

SELECT RAND() AS [RandomNumber]

The result generated by this SELECT statement is as follows (note that your results may be different from the value shown here, hence the name random)

RandomNumber
---------------------
0.34344339282376501

The output of the RAND function will always be a value between 0 and 1.  If you want to generate a random integer number, all you have to do is multiply it by the maximum value you want generated and then get rid of the decimal places.  One way of getting rid of the decimal places is by CASTing it to INT.  Here’s an example of generating a random number with a maximum value of 999,999:

<pre class="sqlscript">SELECT CAST(RAND() * 1000000 AS INT) AS [RandomNumber]

And here’s an example result of this SELECT statement:

RandomNumber 
------------ 
163819

The Downside of the RAND Function

One thing to take note with the RAND function is that if the seed parameter is passed to it, the output will always be the same.  This can be seen with the following:

SELECT RAND(1) AS [RandomNumber]

Running this SELECT statement multiple times with 1 as the seed of the RAND function will always yield the same result:

RandomNumber
---------------------
0.71359199321292355

Another thing to take note with the RAND function is that if it is included in a SELECT statement on a table, the value returned for each row will be the same, as can be seen with the following example.

<pre class="sqlscript">SELECT TOP 10 RAND() AS [RandomNumber], [CustomerID], [CompanyName], [ContactName]
FROM [dbo].[Customers]
RandomNumber         CustomerID CompanyName                         ContactName
-------------------- ---------- ----------------------------------- -------------------
0.21090395019612362  ALFKI      Alfreds Futterkiste                 Maria Anders
0.21090395019612362  ANATR      Ana Trujillo Emparedados y helados  Ana Trujillo
0.21090395019612362  ANTON      Antonio Moreno Taquería             Antonio Moreno
0.21090395019612362  AROUT      Around the Horn                     Thomas Hardy
0.21090395019612362  BERGS      Berglunds snabbköp                  Christina Berglund
0.21090395019612362  BLAUS      Blauer See Delikatessen             Hanna Moos
0.21090395019612362  BLONP      Blondesddsl père et fils            Frédérique Citeaux
0.21090395019612362  BOLID      Bólido Comidas preparadas           Martín Sommer
0.21090395019612362  BONAP      Bon app'                            Laurence Lebihan
0.21090395019612362  BOTTM      Bottom-Dollar Markets               Elizabeth Lincoln

Given this example, if you want to assign a random number for each row in your table, the values assigned to all rows will be the same.  This nature of the RAND function is even highlighted in Books Online:

The NEWID() Way

Here’s a different way of generating a random number without using the RAND mathematical function.  The NEWID system function can be used to generate a random numeric value as can be seen from the following SELECT statement.

<pre class="sqlscript">SELECT ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) AS [RandomNumber]

The NEWID system function returns a unique value of uniqueidentifier data type.  In order to convert this to an integer data type, it first has to be converted to VARBINARY then it can be converted to integer, as can be seen by the two CAST statements.  The resulting integer value can be positive and negative.  If you want it to be just a positive value, then we have to use the absolute value mathematical function ABS.  If negative values are acceptable, then the ABS function can be removed.

Here’s an example of a result when running this SELECT statement (note that the value shown here will be different from your result):

RandomNumber 
------------ 
403663676

Doing the same SELECT statement from the [dbo].[Customers] table of the Northwind database but using the system function NEWID:

SELECT TOP 10 ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) AS [RandomNumber],
       [CustomerID], [CompanyName], [ContactName]
FROM [dbo].[Customers]

This generates the following output:

RandomNumber CustomerID CompanyName                              ContactName
------------ ---------- ---------------------------------------- -------------------
1120919216   ALFKI      Alfreds Futterkiste                      Maria Anders
1227765350   ANATR      Ana Trujillo Emparedados y helados       Ana Trujillo
677704826    ANTON      Antonio Moreno Taquería                  Antonio Moreno
151612960    AROUT      Around the Horn                          Thomas Hardy
755868329    BERGS      Berglunds snabbköp                       Christina Berglund
1566550720   BLAUS      Blauer See Delikatessen                  Hanna Moos
1511953511   BLONP      Blondesddsl père et fils                 Frédérique Citeaux
1280157238   BOLID      Bólido Comidas preparadas                Martín Sommer
1283976604   BONAP      Bon app'                                 Laurence Lebihan
989554291    BOTTM      Bottom-Dollar Markets                    Elizabeth Lincoln