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 Integer Random Number Between Two numbers
In case if you want random numbers generated between two integers then use this syntax:
A + FLOOR(RAND() * (B + 1 - A)); Where: A = The smallest number of the range. B = The largest number of the range.
DECLARE @A INT; DECLARE @B INT; SET @A = 10 SET @B = 100 SELECT @A + FLOOR(RAND() * (@B + 1 - @A)); GO /* Result */ 73
Generate Random Integer Between 0 And Any Integer
For generating random integer between 0 to any number use this formula:
ROUND(RAND() * A, 0); Where: A = The largest number of the range.
This script will generate random integer between 0 to 100
SELECT ROUND(RAND() * 100, 0); GO /* Result */ 26
Generate Random Float Between 0 And Any Number
To generate random float number between 0 and any number use this formula:
RAND() * A; Where: A = The largest number of the range.
This script will generate random float number between 0 to 100
SELECT RAND() * 100; /* Result */ 27.787772112756
- About RAND() math function at Microsoft Docs.
4 thoughts on “How To Generate Random Numbers In SQL Server?”
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().
Correct FLOOR Should be correct
Yes. I’ve changed the example now.
You are correct. FLOOR will give accurate answer while generating random integer between two integers. I’ve changed the example now. Thank you for pointing out.