|
|
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:
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.
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:
"REMARKS: Repetitive invocations of RAND() in a single query will produce
the same value."
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.
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
|