How To Generate Random Numbers In SQL Server?

In SQL Server there is a built-in function RAND() to generate random number. RAND() will return a random float value between 0 to 1.

Usage RAND() As It Is

If you use RAND() as it is or by seeding it, you will get random numbers in decimals ranging between 0 and 1.

SELECT RAND(), RAND(5);
SELECT RAND(), RAND(5);

/* Result */
0.5651861527384644            0.713666525097956
0.454560299686459             0.713666525097956
Generate Random Numbers In SQL Server

Generate Integer Random Number Between Two numbers

In case if you want random numbers generated between two integers then use this syntax:

Syntax

A + FLOOR(RAND() * (B + 1 - A));
Where:
A = The smallest number of the range.
B = The largest number of the range.

Example

DECLARE @A INT;
DECLARE @B INT;
 
SET @A = 10
SET @B = 100
 
SELECT @A + FLOOR(RAND() * (@B + 1 - @A));
GO

/* Result */
73
Generate Integer Random Number Between Two numbers

Generate Random Integer Between 0 And Any Integer

For generating random integer between 0 to any number use this formula:

Syntax

ROUND(RAND() * A, 0);
Where:
A = The largest number of the range.

Example

This script will generate random integer between 0 to 100

SELECT ROUND(RAND() * 100, 0);
GO

/* Result */
26
Generate Random Integer Between 0 And Any Integer

Generate Random Float Between 0 And Any Number

To generate random float number between 0 and any number use this formula:

Syntax

RAND() * A;
Where:
A = The largest number of the range.

Example

This script will generate random float number between 0 to 100

SELECT RAND() * 100;

/* Result */
27.787772112756

Reference


4 thoughts on “How To Generate Random Numbers In SQL Server?”

  1. The application of the ROUND() function in the examples above appears wrong to me. Example: if I desire random integers between 1 and 6 and the result of RAND() happens to be 0.99, then the result of A + ROUND(RAND() * (B + 1 – A), 0) is 7, which is out of range. I believe the correct function to be applied would be FLOOR().

    Reply

Leave your thoughts...

This site uses Akismet to reduce spam. Learn how your comment data is processed.