Random Number Generator
There are many methods to generate random numbers in SQL Server.
Method 1: Generate Random Numbers (Int) between Rang
---- Create the variables for the random number generation
DECLARE @Random INT;
DECLARE @Upper INT;
DECLARE @Lower INT
---- This will create a random number between 1 and 999
SET @Lower = 1 ---- The lowest random number
SET @Upper = 999 ---- The highest random number
SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
SELECT @Random
Method 2: Generate Random Float Numbers
SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()) )
Method 3: Random Numbers Quick Scripts
---- random float from 0 up to 20 - [0, 20)
SELECT 20*RAND()
-- random float from 10 up to 30 - [10, 30)
SELECT 10 + (30-10)*RAND()
--random integer BETWEEN 0
AND 20 - [0, 20]
SELECT CONVERT(INT, (20+1)*RAND())
----random integer BETWEEN 10
AND 30 - [10, 30]
SELECT 10 + CONVERT(INT, (30-10+1)*RAND())
Method 4: Random Numbers (Float, Int) Tables Based on Time
DECLARE @t TABLE( randnum float )
DECLARE @cnt INT; SET @cnt = 0
WHILE @cnt <=10000
BEGIN
SET @cnt = @cnt + 1
INSERT INTO @t
SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()) )
END
SELECT randnum, COUNT(*)
FROM @t
GROUP BY randnum
Method 5: Random number on a per-row basis
---- The distribution is pretty good however there are the occasional peaks.
---- If you want to change the range of values just change the 1000 to the maximum value you want.
---- Use this as the source of a report server report and chart the results to see the distribution
SELECT randomNumber, COUNT(1) countOfRandomNumber
FROM (
SELECT ABS(CAST(NEWID() AS binary(6)) %1000) + 1 randomNumber
FROM sysobjects) sample
GROUP BY randomNumber
ORDER BY randomNumber
Watch a 60-second video on this subject of Random Number Generator Script.
Well, that’s it for today. If you liked this video, please do not forget to subscribe to my YouTube Channel – SQL in Sixty Seconds.
Here are my few recent videos and I would like to know what is your feedback about them.
- Copy Database – SQL in Sixty Seconds #169
- 9 SQL SERVER Performance Tuning Tips – SQL in Sixty Seconds #168
- Excel – Sum vs SubTotal – SQL in Sixty Seconds #167
- 3 Ways to Configure MAXDOP – SQL in Sixty Seconds #166
- Get Memory Details – SQL in Sixty Seconds #165
- Get CPU Details – SQL in Sixty Seconds #164
- Shutdown SQL Server Via T-SQL – SQL in Sixty Seconds #163
- SQL Server on Linux – SQL in Sixty Seconds 162
- Query Ignoring CPU Threads – SQL in Sixty Seconds 161
- Bitwise Puzzle – SQL in Sixty Seconds 160
- Find Expensive Queries – SQL in Sixty Seconds #159
- Case-Sensitive Search – SQL in Sixty Seconds #158
- Wait Stats for Performance – SQL in Sixty Seconds #157
- Multiple Backup Copies Stripped – SQL in Sixty Seconds #156
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
113 Comments. Leave new
We can do it in a single SQL statement:
Select ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT))/10000
Output:
Posted By:
Shubham Saxena
[email removed]
Do it this way:
SELECT FLOOR(RAND() * POWER(CAST(10 as BIGINT), 4)) — 4 is the number of digits required in the random number.
There is a problem with this solution. Distribution of random numbers is not linear. They are not evenly distributed. I tested 50000 numbers
id howmany
5 6221
1 6390
3 12362
2 12509
4 12517
1 and 5 are the least probabilities you will get and 2,3,4 will be appearing more than 1 and 5. The beauty of a random algorithm is linear distribution which I can not find in this algorithm,
I need to assign a random rank to each row in a table when i select the records. Each time i should get different orders for the same record.
When i run first time it should be some thing like
Id Name Rank
1 AAA 121
2 BBB 2
3 CCC 192
4 DDD 71
and when i run next time it should be like
Id Name Rank
1 AAA 41
2 BBB 219
3 CCC 189
4 DDD 514
I found same result. Glad to see that I am not the only one. Pinal, this does not produce a random distribution: SELECT @Random = ROUND(((@Upper – @Lower -1) * RAND() + @Lower), 0)
I realized that RAND doesn’t work the way I thought.
I am trying to get random Numerics 22 digits
—- Create the variables for the random number generation
DECLARE @Random numeric(22,0);
DECLARE @Upper numeric(22,0);
DECLARE @Lower numeric(22,0);
DECLARE @i as smallint = 0;
WHILE @i < 5
Begin
—- This will create a random number between 1 and 999
SET @Lower = 1 —- The lowest random number
SET @Upper = 9999999999999999999999 —- The highest random number
SELECT @Random = ROUND(((@Upper – @Lower -1) * RAND() + @Lower), 0)
SELECT CONVERT(numeric(22,0), @Random)
SET @i = @i + 1
END
However I am getting trailing zeros??? Anythoughts
5269217793094762600000
2069010432392735200000
9874429441046535500000
4036267276971462400000
4622200941923071400000
Got it – select CONVERT(numeric(22,0),RAND() * 10000000000000000000000) + CONVERT(numeric(22,0),RAND() * 100000)
hai pinal dev ji,
can i get full course of SQL server from scratch to real time else, can i get guide for sqlauthority site map to learn SQL from your website (i see random posts)
Sorry, I don’t have any such offering for SQL from scratch. I do have special course. http://blog.sqlauthority.com/sql-server-performance-tuning-practical-workshop/
example : i have a table, my table having coloumn a,b,c . coloumn a (Primary key) then coloumn b and c data type is integer, how to create select between numer range cloumn b and c, when them input value random ?