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
Hi i am having 1 column location ids ( int )
My task is to get top 2 random rows from tat column
I have query like this
select top 2 locationid from profile order by newid()
this one is good for 500K only
But i am having 20 millions rows tables in that it takes 4 sec
its creating a hell in tunning …
Can any one help me out in this pls Thank u
You can do it like this.
select top 2 locationid from profile tablesample(1000)
amit singh add this also,,,
select Top 2 Userid from [User] tablesample(100)
order by newid()
Hello Mohan,
Using the ORDER BY clause is the reason of high execution time of your query. Instead of ORDER BY, use TABLESAMPLE option to get random rows.
Regards,
Pinal Dave
thx, nice info.
Do you know how the RAND function is implemented? What underlying algorithm is being used?
Thx.
It just generates the numbers between 0 and 1. It is similar to the one available in C language
Dave,
I need you help in creating a random number between 0 and 0.999999
but the methods you havce defined above are too complex for me to understand as i am new to sql.
I want to create this random number fields also for new varibales with a tabel so that i can keep creating new random numbers when needed.
please help me
Thanks pete
This code will do what you needed
select cast(rand() as decimal(8,6))
Here’s one way to do this if you ever need multiple random numbers generated between 0 and 1.
DECLARE @Counter int
CREATE TABLE ##Random (Number decimal(5,2)) — or decimal(8,6))
SET @Counter = 1
WHILE @Counter < 11 –change the number 11 to how ever many random numbers you want generated
BEGIN
INSERT INTO ##Random (Number)
SELECT CAST(RAND() as decimal(5,2)) as Number — or decimal(8,6))
SET @Counter = @Counter + 1
END
SELECT *
FROM ##Random
–DROP TABLE ##Random
Dave i forgot to mention that once the number is created it requires to be stored in a decimla 8,6 feild permanently
thanks once again
Pete
insert into table(decimal_col)
select cast(rand() as decimal(8,6))
hi
can someone help me i have 70 questionnaire records in my table and i want to show 50 records in random using stored procedure in ms sql server 2000..
use it
select top 50 ques from questionnaire order by newid()
Hi.
I want to generate 6 digits random number for employee ID’s in my company, so the ganerated numbers must be unique in table.
any suggestion..
Thanks..
Check the uniqueness
select ABS(CHECKSUM(NEWID())+999999)%1000000
Best site ever!
hello i need a procedure to insert a random value and select the this randomly
select CHECKSUM(NEWID())%1000
UPDATE seeker_profile SET career_level_id= CEIL(RANDOM()*7);
Random from 1 to 7
Thanks Man, your post helped me so much
what should i insert in A to get non repeated random number in Seat_No column
SqlCommand com = new SqlCommand(“UPDATE Table1
SET Seat_No ='” + ” A “+ “‘
Where Applicant_NIC='” + ” ” + TextBox3.Text + ” ” + “‘”, conn);
Hi,
I am trying to write a sql for DB2 table where I am using the following:
– Inserting into an existing table
– Using a random number which already does not exist in this table.
– Can’t use stored procedure.
Any assistance will be great.
insert into table(col)
select * from
(
select cast(cast(newid() as varbinary(2)) as int) random_no
) as t where not exists (select * from table where col=random_no )
Thanks Madhivanan:
are you sure this working?
Consider table name accounts
and columns name account_id
Yes it will work. Did you try?
Hi Pinal,
Thank you so much for your blog. I am so often googling and finding the answers at your site. Please keep up the great work. Jean
DECLARE @Random INT, @Upper INT, @Lower INT, @Count INT
set @Count = 0
DECLARE @1 int , @2 int, @3 int, @4 int, @5 int, @6 int
SELECT @1 = 0, @2 = 0, @3 = 0, @4 = 0, @5 = 0, @6 = 0
SET @Lower = 1 —- The lowest random number
SET @Upper = 6 —- The highest random number
WHILE @Count < 1000
BEGIN
SELECT @Random = ROUND(((@Upper – @Lower -1) * RAND() + @Lower), 0)
IF @RANDOM = 1 SET @1 = @1 + 1
IF @RANDOM = 2 SET @2 = @2 + 1
IF @RANDOM = 3 SET @3 = @3 + 1
IF @RANDOM = 4 SET @4 = @4 + 1
IF @RANDOM = 5 SET @5 = @5 + 1
IF @RANDOM = 6 SET @6 = @6 + 1
SET @Count = @Count + 1
END
SELECT @1 , @2 , @3 , @4 , @5 , @6
Hi,
I have a problem on random number and great some one can help.
We have a table which has 100 records and i want to generate random number between 40 and 70 for each record. So i need unique number generated for each record and it should be between 40 and 70. I dont mind if we get float values as well.
If anyone can answer it would be of immense help.
Regards
Rajesh
The chance of getting random integer number is not equal by using ROUND function. I guess FLOOR or CEILING is better choice –
SELECT @Random = FLOOR(@Lower + RAND() * (@Upper – @Lower + 1))
Hi,
Pinal Dave
i had a problem i want to create 6 digit non repeating random number like 123456 if 122345 (wrong digits)
how can i code it