SQL Server RAND() Function

Summary: in this tutorial, you will learn how to use the SQL Server RAND() function to return a pseudo-random float value.

Introduction to the SQL Server RAND() function

The RAND() function is a math function that allows you to generate a random value.

Here’s the basic syntax of the RAND() function:

RAND([seed])Code language: SQL (Structured Query Language) (sql)

In this syntax:

• seed is an integer that provides a seed value. If you use a seed value, the RAND() function will always return the same result. If you don’t specify a seed value, SQL Server will assign a random seed value.

The RAND() function returns a float value that ranges from 0 through 1, exclusively. This means that the RAND() function may return 0 but never return 1.

SQL Server RAND() function examples

Let’s explore some examples of using the SQL Server RAND() function.

1) Basic SQL Server RAND() function example

The following statement uses the RAND() function to generate a random float value between 0 and 1:

SELECT RAND() AS random;Code language: SQL (Structured Query Language) (sql)

Sample Output:

random
-----------------
0.943996112912269Code language: SQL (Structured Query Language) (sql)

2) Generate random numbers in a range

If you need a random number within a specific range, you can use the RAND() function as follows:

SELECT @low + (RAND() * @high) AS random;Code language: CSS (css)

For example, the following statement returns a random number between 10 and 90:

SELECT 10 + (RAND() * 90) AS random;Code language: PHP (php)

Sample output:

random
----------------
10.375665720547Code language: CSS (css)

4) Generating random integers within a range

To generate a random integer within a range from @low to @high, you can combine the RAND() function with the FLOOR() function:

FLOOR((RAND() * (@high - @low)) + @low)Code language: CSS (css)

For example, the following statement uses the RAND() function with the FLOOR() function to generate a random integer between 10 and 90:

SELECT
  FLOOR((RAND() * (100 - 10 + 1)) + 10) random;Code language: SQL (Structured Query Language) (sql)

Sample output:

random_integer
--------------
49Code language: SQL (Structured Query Language) (sql)

How the query works:

• RAND(): Generates a random float value between 0 and 1.
• (100 - 10 + 1): Calculates the range of integers between 10 and 100. The +1 is added because we want to include both 10 and 100.
• RAND() * (100 - 10 + 1): Generates a random float value within the range calculated in step 2.
• (RAND() * (100 - 10 + 1)) + 10: Shifts the random float value to start from 10.
• FLOOR((RAND() * (100 - 10 + 1)) + 10): Rounds the float value to the nearest integer.

5) Generating a random number with a seed

The following example uses the RAND() function with a seed value. It’ll return the same random number if you execute the statement multiple times:

SELECT RAND(1) random;

Sample output:

random
-----------------
0.713591993212924Code language: CSS (css)

Summary

• Use the RAND() function to generate a random float number between 0 and 1.