SQL Server RAND(): Get Random Numbers
In SQL Server, the
RAND() function returns a random number between 0 and 1, excluding 0 & 1.
It returns the pseudo-random float value.
seed: Optional. An integer value to generate a random number. If it is not provided, SQL Server assigns different seed value on each execution.
Note: For a specified seed value, the return value is always the same.
Returns a float between 0 through 1.
In this simple example,
RAND() is called without a seed value. Here the
RAND() function returns a different value every time it is executed because
RAND() uses a random seed value each time.
SELECT RAND() AS WithoutSeed;
The above output may be different in your local SQL Server. It returns the different random number on each execution because it internally passes different seed value each time.
In the following example, the
RAND() uses a seed value and the return value is always the same for a given seed.
SELECT RAND(7) AS WithPosSeed;
In this example, the
RAND() uses a negative seed value and the return value is always the same for a given seed similar to a positive seed.
SELECT RAND(-9) AS WithNegSeed;
Generate Random Number Between Specific Numbers
use the following formula to generate a random integer value between the two numbers:
SELECT FLOOR(RAND() * (b-a+1)) + a
In the above formula,
a is the smallest number and
b is the largest number in the range in which you want to generate a random number (inclusive of a & b).
For example, the following generates a random integer between 10 and 20.
Since the FLOOR() function is used with
RAND(), the result would be integer.
SELECT FLOOR(RAND() * (20-10 + 1)) + 10 AS RandInt;