Home Library Learn Downloads Support Community
Sign in | United States - English | |
3 out of 7 rated this helpful - Rate this topic

RAND (Transact-SQL)

SQL Server 2008

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

Topic link iconTransact-SQL Syntax Conventions

Copy 

RAND ( [ seed ] )
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.

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.

Copy 
SELECT RAND(100), RAND(), RAND()

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

Copy 
DECLARE @counter smallint;
SET @counter = 1;
WHILE @counter < 5
   BEGIN
      SELECT RAND() Random_Number
      SET @counter = @counter + 1
   END;
GO
Did you find this helpful?
(1500 characters remaining)
Community Content Add
Annotations FAQ
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.

History
  • 4/24/2009
  • jZe