SQL Server RAND() function | Generating random number within the specific range

SQL Server RAND() function | Generating random number in a range

In this article, we will see RAND() function and also see how to generate a random number in a range using RAND() function.

RAND() Function

The RAND function can be used to return a random number. It will return a value between 0 and 1 such as value greater than 0 and less than 1 ( 0> value <1). By default, this function will return a completely random number, if the seed value is provided then a repeatable sequence of the random number will be returned.

Syntax: RAND([seed])

Return Type: Float

Examples

SELECT RAND()    -- Result - 0.182458908613686
select RAND(10)  -- Result - 0.713759689954247
select RAND(-10)  -- Result - 0.713759689954247

Generating Random Number within the specific range

In development, we often need to generate a random number, sometimes random number in a range e.g. 1 to 1000. 

Random Decimal Range

To create a random decimal number between two values (range), you can use the following formula:

SELECT (MAX_VAL-MIN_VAL) * RAND() + MIN_VAL

Where MAX_VAL is the maximum/upper/highest value and MIN_VAL is the minimum/lower/smallest value that you want to generate a random in a range

Random Integer Range

To create a random integer number between two values (range), you can use the following formula:

SELECT ROUND((MAX_VAL-MIN_VAL) * RAND() + MIN_VAL,0)
OR 
SELECT FLOOR((MAX_VAL-MIN_VAL) * RAND() + MIN_VAL)

Where MAX_VAL is the maximum/upper/highest value and MIN_VAL is the minimum/lower/smallest value that you want to generate a random in a range. Both methods will results the same output.

Examples

Let us see the queries to return a random number between 2000 and 3000.

DECLARE @MIN_VALUE INT = 2000
DECLARE @MAX_VALUE INT = 3000
-- Random Decimal Range
SELECT (@MAX_VALUE - @MIN_VALUE) * RAND() + @MIN_VALUE
-- RESULT 2182.45890861369
SELECT (@MAX_VALUE - @MIN_VALUE) * RAND(10) + @MIN_VALUE
-- RESULT 2713.75968995425
SELECT (@MAX_VALUE - @MIN_VALUE) * RAND(-10) + @MIN_VALUE
-- RESULT 2713.75968995425
-- Random Integer Range
SELECT ROUND(((@MAX_VALUE - @MIN_VALUE) * RAND() + @MIN_VALUE), 0)
-- RESULT 2182
SELECT FLOOR(((@MAX_VALUE - @MIN_VALUE) * RAND(100) + @MIN_VALUE))
-- RESULT 2715
SELECT FLOOR(((@MAX_VALUE - @MIN_VALUE) * RAND(12345) + @MIN_VALUE))
-- RESULT 2943

Thanks for your time. If you have any suggestions or queries, please comment below.