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
Reference:Â Pinal Dave (http://blog.SQLAuthority.com)
113 Comments. Leave new
Hello, what would be the best way to pull a random value from a list of values. I just need to select one value from a set list I have?
select top 1 val from table
order by newid()
Thanx a lottt… u saved my day
Please help me for below qery , I m checking series between two number
This is my sql qry.
SET @count=( SELECT COUNT(*) FROM M_COIINDENT_MASTER
where ( FromCOINumber@FromCOINumber))
IF @count > 0
SET @msg=@msg+’ From Limit belong to some another series ‘;
print @msg
Your random number using just (SELECT ABS(CAST(NEWID() AS binary(6)) %1000) + 1 ) as RandID beats the RAND() function hands down!!!!
as per my experience randomizing number result in duplicate such as number “23” repeating after creation . please elaborate
E.g: on execurte 23 another execute 23 …
Please Help!
sorry I was trying to say check:
This is the best alternative to NewId() I found. NewId() has performance issues when it comes to large record sets.
This works for me Thanks
Simple, effective – quite nearly poetic Pinal! Love it.
Create table COrder
(
AutoID bigint identity(1,1),
RndID bigint default cast (Rand() * 3000 as Bigint) Unique,
name varchar(10),
CreatedOn Datetime default getdate()
)
If I insert a record dynamically [using asp.net] in this table and previous random number is generated then what would happen…………how to solve this that after repeating rndid it regenerate script… help me………
use checksum(newid())
i want to generate table
create script
alter script
insert script
update script
by using command?
can you please tell me how it is possible by using sql commands
Hi All, Can I generate multiple Random numbers, because when i am writing [select rand()] it gives me one random number. and if i want 10 random numbers then what should i write.
Hi
How to auto generate number between 10000 to 99999 without repeat number in sql server
Hello, I have the same question:
How to auto generate number between 10000 to 99999 without repeat
I’m trying to modify your example:
SELECT randomNumber , COUNT(1) countOfRandomNumber
FROM (
SELECT ABS(Checksum(NEWID()) %1000000) + 1 randomNumber
FROM sysobjects) sample
GROUP BY randomNumber
ORDER BY randomNumber
but I wonder how to set interval beetwen two numbers
Ok,
I have interval between 100000 and 999999
SELECT randomNumber, COUNT(1) countOfRandomNumber
FROM (
SELECT ABS(CAST(NEWID() AS binary(6)) %900000) + 100000 randomNumber
FROM sysobjects) sample
GROUP BY randomNumber
ORDER BY randomNumber
or
SELECT randomNumber, COUNT(1) countOfRandomNumber, COUNT(randomNumber)
FROM (
select ABS(CHECKSUM(NEWID()) %900000) + 100000 randomNumber
FROM sysobjects) sample
GROUP BY randomNumber
ORDER BY randomNumber
but now I think about second problem. I want 500 random number but query gives me only about 90.
Hello,
I have a car table. I want to set the carQuestionId for EACH car record to be either 1, 2, or 3. I cannot figure out how to apply your @Random code to get this to work for carQuestionId.
Pseudo Code:
update car set carQuestionId = (1, 2 or 3)
where carStatus = ‘g’ and carType = ‘Honda’
Please help.
Hello,
I want to solve this problem can any one help ?
-How to generate 1000 records randomly in Database?
Details Question:
User will insert 2000 as input and in database one field(Input) will be there.
In this field 1000 record should be generated randomly between 0.50 to 3.00 i.e. records can be any number between 0.50 to 3.00 and importantly the SUM of these 1000 records must be 2000.
Pinal et al,
I have a slightly more complicated Rand question. I have two tables: one that stores promotion records and a second that is used to calculate a random daily winner from the first table. Their structures are:
table1 (
rec_id (PK, int, not null),
promo_id (int, not null),
date (date, not null),
user_id (nvarchar(10), not null)
)
table2 (
id (PK, int, not null),
PromoID (int, not null),
rec_id (int, not null)
)
So obviously looking at this, table one has a PK for the record id that is incremented by 1 on each insert. I might have any number of promotions running simultaneously and need to create a random winner (table1.rec_id) on a daily basis for each promotion (table1.promo_id) and insert the results into table2.
I tried:
INSERT INTO prT_Daily_Winner (PromoID, rec_id)
SELECT DISTINCT
promo_id,
(abs(checksum(NewId())) % (SELECT COUNT(*) FROM prT_Promo_Records b WHERE b.promo_id=a.promo_id AND CONVERT(varchar,[date],101)=CONVERT(varchar,GETDATE(), 101)) +1) as RandNum
FROM
dbo.prT_Promo_Records a
WHERE
CONVERT(varchar,[date],101)=CONVERT(varchar,GETDATE(), 101)
GROUP BY promo_id
The problem I run into is that setting the upper and lower based on my rec_id doesn’t work since I may get results where the Random number doesn’t correlate to the specific promotion going on.
Any assistance would be appreciated. Thank you!
Dave, Thanks so much for providing this. Using your Method 1, I specified upper and lower bounds to be 1 and 16. I ran your formula 1000 times in a loop and did not get 16 returned once! I really need to use your formula in a game I am writing, but need 16 returned sometimes! How can I accomplish this with your Method 1? Thanks, Bruce Wilson
DECLARE @Random INT;
DECLARE @Upper INT;
DECLARE @Lower INT;
DECLARE @intLoop INT;
SET @Lower = 1 —- The lowest random number
SET @Upper = 16 —- The highest random number
SET @intLoop = 1
WHILE @intLoop <= 1000
BEGIN
—- This will create a random number between 1 and 999
SET @Random = ROUND(((@Upper – @Lower -1) * RAND() + @Lower), 0)
IF @Random = 16
PRINT 'Returned 16'
SET @intLoop = @intLoop + 1
END
These are greats approaches.. thanks for sharing.
Yes, -1 in first method is redundant, it should be removed, because now it will never return the upper value. It is enough just to remove it:
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) * RAND() + @Lower), 0)
SELECT @Random