The SQL RAND function generates a random number between the range of 0 and 1. The number can be greater than equal to zero but less than one.
The RAND function gives a completely random number if no seed value is specified. The RAND function produces a repetitive random number for all the successive RAND calls in an SQL connection in case of a provided seed value.
SQL RAND Usage Notes
The seed argument of the SQL RAND function is an integer value. The seed value datatype can be int, smallint, or tinyint. If the seed value is not specified, the SQL server assigns a random seed value to the RAND function.
In the case of a specified seed, the output is always the same for that value. Also, all the subsequent RAND calls after a seeded RAND function will produce results according to the seeded value.
The number generated is a pseudo-random number.
The RAND function returns a float datatype.
RAND( seed (optional))
SQL RAND Examples
The following query is a simple example of using a RAND function.
/* Answer */ SELECT RAND() AS RandomNumber
The query returns a different random number on each run.
/* Answer */ SELECT RAND(500) Seed500, RAND() Rand1, RAND() Rand2
The above query runs the first RAND function with a seeded value. The following RAND functions generate output based on the seeded call. Moreover, if we run this query multiples times for a single SQL connection, the output numbers remain the same.
Generate A Random Number Within a Range
The following syntax generates a random number within a range from the value a up to but not including b.
Here is a more tangible example where we generate a random value, r, from 8 to 20 noninclusive ( 8 <= r < 20 )
/* Answer */ SELECT RAND()*(20-8)+8 r;