-
Aggregates a subset of random numbers
-
Generate a random number
-
Requests a range of numbers from a subset
-
Selects an random number from subset
EXPLANATION
SQL Server: RAND Function
This SQL Server tutorial explains how to use the RAND function in SQL Server (Transact-SQL) with syntax and examples.
Description
In SQL Server (Transact-SQL), the RAND function can be used to return a random number or a random number within a range.
Syntax
The syntax for the RAND function in SQL Server (Transact-SQL) is:
RAND( [seed] )
Parameters or Arguments
- seed
- Optional. If specified, it will produce a repeatable sequence of random numbers each time that seed value is provided.
Note
- The RAND function will return a value between 0 and 1 (not inclusive), so value > 0 and value < 1.
- The RAND function will return a completely random number if no seed is provided.
- The RAND function will return a repeatable sequence of random numbers each time a particular seed value is used.
Random Decimal Range
To create a random decimal number between two values (range), you can use the following formula:
SELECT RAND()*(b-a)+a;
Where
a is the smallest number and
b is the largest number that you want to generate a random number for.
SELECT RAND()*(25-10)+10;
The formula above would generate a random decimal number between 10 and 25, not inclusive.
TIP: This formula would generate a random decimal number that is > 10 and < 25 but it would never return exactly 10 or 25.
Random Integer Range
To create a random integer number between two values (range), you can use the following formula:
SELECT FLOOR(RAND()*(b-a+1))+a;
Where
a is the smallest number and
b is the largest number that you want to generate a random number for.
SELECT FLOOR(RAND()*(25-10+1))+10;
The formula above would generate a random integer number between 10 and 25, inclusive.
TIP: This formula would generate a random integer number that is >= 10 and <= 25.
Applies To
The RAND function can be used in the following versions of SQL Server (Transact-SQL):
- SQL Server 2017, SQL Server 2016, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005
Example of Random Number
Let's explore how to use the RAND function in SQL Server
(Transact-SQL) to generate a random number between 0 and 1, not
inclusive.
For example:
SELECT RAND();
Result: 0.143811355073783 (no seed value, so your answer will vary)
SELECT RAND(9);
Result: 0.713741056982989 (with seed value of 9)
SELECT RAND(-5);
Result: 0.713666525097956 (with seed value of -5)
Example of Random Decimal Range
Let's explore how to use the RAND function in SQL Server
(Transact-SQL) to generate a random decimal number between two numbers
(ie: range).
For example, the following would generate a random decimal value
between 1 and 10, not inclusive (random number would be greater than 1
and less than 10):
SELECT RAND()*(10-1)+1;
Result: 5.09104269717813 (no seed value, so your answer will vary)
SELECT RAND(9)*(10-1)+1;
Result: 7.4236695128469 (with seed value of 9)
SELECT RAND(-5)*(10-1)+1;
Result: 7.42299872588161 (with seed value of -5)
Example of Random Integer Range
Let's explore how to use the RAND function in SQL Server
(Transact-SQL) to generate a random integer number between two numbers
(ie: range).
For example, the following would generate a random integer value between 10 and 20, inclusive:
SELECT FLOOR(RAND()*(20-10+1))+10;
Result: 19 (no seed value, so your answer will vary)
SELECT FLOOR(RAND(9)*(20-10+1))+10;
Result: 17 (with seed value of 9)
SELECT FLOOR(RAND(123456)*(20-10+1))+10;
Result: 10 (with seed value of 123456)
