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.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
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 ?