SQL Server RAND() Function

The RAND() function in SQL Server generates pseudo-random floating-point numbers within the range of 0 (inclusive) and 1 (exclusive). It is a function that can produce different random values each time it is called unless a specific seed is provided which results in a repeatable sequence of numbers.

In this article, We will learn about SQL Server RAND() Function in detail with the help of various examples and so on.

SQL Server RAND() Function

• The RAND() function in SQL Server generates a pseudo-random floating-point number between 0 (inclusive) and 1 (exclusive).
  
• Each time we call RAND(), it returns a different random value unless you provide a specific seed.

Syntax

RAND() function syntax is:

RAND(seed)

Parameter

RAND() function accepts a parameter as given below:

• seed: If the seed 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.

Key Points:

• Range: The value returned by RAND() is always greater than or equal to 0 and less than 1.
• Deterministic with Seed: If a seed value is provided, the sequence of numbers generated by RAND() will be repeatable and deterministic.
• Non-Deterministic without Seed: If no seed is provided, the function will produce different random numbers on each execution.

Example of SQL Server RAND() Function

Let's look at some examples of the RAND() function in SQL Server.

Example 1:

In this example, we return a random decimal value.

Query:

SELECT RAND();

Output:

0.37892290119984562

Example 2:

In this example, we return a random decimal value with seed value =5.

Query:

SELECT RAND(5);

Output :

0.71366652509795636

Example 3:

In this example, we will use RAND() function with variables and getting a random number between in the range of [ 2, 8 ) using RAND function.

Query:

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

Output :

7.0

This query declares two integer variables, @i and @j, and sets their values to 2 and 8, respectively. It then generates a random floating-point number between @i and @j using the RAND() function, and the FLOOR() function is applied to round down the result to the nearest integer. The final output is a random integer between 2 and 7.

Conclusion

The RAND() function in SQL Server offers a straightforward way to generate random numbers, with the ability to control randomness through seeding. By providing a seed, you can ensure that the sequence of random numbers is reproducible, which is useful for testing and debugging. Without a seed, the function produces non-deterministic results, providing a different random number with each call.