RAND() Function in SQL Server

RAND() function :
This function in SQL Server is used to return a random decimal value and this value lies in the range greater than and equal to zero (>=0) and less than 1. If we want to obtain a random integer R in the range i <= R < j, we have to use the expression “FLOOR(i + RAND() * (j − i))”.

Features :

• This function is used to give a random decimal value.
• The returned value lies in between 0 (inclusive) and 1 (exclusive).
• If this function does not accept any parameter, it will returns a completely random number.
• If this function takes a parameter, it will returns a repeatable sequence of random numbers.
• This function accepts optional parameter.
• This function uses a formula
  “FLOOR(i + RAND() * (j − i))” to get a random integer R,
  where R lies in the range of “i <= R < j”.

Syntax :

RAND(N)

Parameter :
This method accepts a parameter as given below :

• N : If N is specified, it returns a repeatable sequence of random numbers. If no N is specified, it returns a completely random number. It is optional and it works as a seed value.

Returns :
It returns a random number between 0 (inclusive) and 1 (exclusive).

Example-1 :
Getting a random value between 0 and 1.

SELECT RAND();

Output :

0.37892290119984562

Example-2 :
Getting a random decimal number with seed value of 5.

SELECT RAND(5);

Output :

0.71366652509795636

Example-3 :
Using RAND() function with variables and getting a random number between in the range of [ 2, 8 ) using RAND Function. Here, we will use the expression : FLOOR(i + RAND() * (j − i)) for generating the random number. Here, i is 2 and j is 8.

DECLARE @i INT;
DECLARE @j INT;
SET @i = 2;
SET @j = 8;
SELECT FLOOR(@i + RAND()*(@j-@i));

Output :

7.0

Example-4 :
Getting a random value between in the range [ 3, 9 ] using RAND Function. Here, we will use the expression : FLOOR(i + RAND() * (j − i + 1)) for generating the random number. Here i is 3 and j is 9.

SELECT FLOOR(3 + RAND()*(9 - 3 + 1));

Output :

9.0

Application :
This function is used to return a random number between 0 (inclusive) and 1 (exclusive).

My Personal Notes arrow_drop_up

Save