Using RAND
The RAND function calculates a random floating-point number from 0 through 1, and can optionally take a tinyint, int, or smallint value for the starting point of the random number to calculate.
The following example calculates two random numbers. The first RAND() function lets SQL Server 2005 pick the seed value, and the second RAND() function uses the value of 3 for the starting position.
SELECT RAND(), RAND(3)
The RAND function is a pseudorandom number generator that operates in a manner similar to the C run-time library rand function. If no seed is provided, the system generates its own variable seed numbers. If you call RAND with a seed value, you must use variable seed values to generate random numbers. If you call RAND multiple times with the same seed value, it returns the same generated value. The following script returns the same value for the calls to RAND because they all use the same seed value:
SELECT RAND(159784)
SELECT RAND(159784)
SELECT RAND(159784)
A common way to generate random numbers from RAND is to include something relatively variable as the seed value, such as adding several parts of a GETDATE:
SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()) )
When you use an algorithm based on GETDATE to generate seed values, RAND can still generate duplicate values if the calls to RAND are made within the interval of the smallest datepart used in the algorithm. This is especially likely when the calls to RAND are included in a single batch. Multiple calls to RAND in a single batch can be executed within the same millisecond. This is the smallest increment of DATEPART. In this case, incorporate a value based on something other than time to generate the seed values.
See Also
Other Resources
RAND (Transact-SQL)
Mathematical Functions (Transact-SQL)