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)) + 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;