Transact-SQL Reference


SQL Server 2008 Books Online (October 2009)
RAND (Transact-SQL)

Returns a pseudo-random float value from 0 through 1, exclusive.

Topic link icon Transact-SQL Syntax Conventions

Syntax
RAND ( [ seed ] )
Arguments
seed

Is an integer expression (tinyint, smallint, or int) that gives the seed value. If seed is not specified, the SQL Server Database Engine assigns a seed value at random. For a specified seed value, the result returned is always the same.
Return Types

float

Remarks

Repetitive calls of RAND() with the same seed value return the same results.

For one connection, if RAND() is called with a specified seed value, all subsequent calls of RAND() produce results based on the seeded RAND() call. For example, the following query will always return the same sequence of numbers.

SELECT RAND(100), RAND(), RAND()
Examples

The following example produces four different random numbers that are generated by the RAND function.

DECLARE @counter smallint;
SET @counter = 1;
WHILE @counter < 5
   BEGIN
      SELECT RAND() Random_Number
      SET @counter = @counter + 1
   END;
GO
See Also

Reference

Mathematical Functions (Transact-SQL)

Other Resources

Using RAND

Help and Information

Getting SQL Server 2008 Assistance
Tags :


Community Content

jZe
Common Pitfalls (and workarounds)
Update X set X.a = Rand()


Will write the same value for all X.a (Rand is only executed once per statement)
An alternative is to use an integer seed derived from the data 

Update X set X.a = Rand(X.AnIntVal)


Also

Select X.a from X order by Rand()


Will not order data randomly
An alternative is to use NewID(). I've found this slow on large datasets (Please post alternatives here)

Select X.a from X order by NewID()


These are quick and dirty solutions. For a more complete solution see http://msdn.microsoft.com/en-us/library/aa175776(SQL.80).aspx for random sampling.


Page view tracker