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.
RAND(seed)Parameters
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.
Return Value
Returns a float between 0 through 1.
Example 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.
Example 2:
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;
Example 3:
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)) + aIn 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;