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.

Was this tutorial helpful?