# SQL SERVER – Random Number Generator Script – SQL Query

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.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

## SQL SERVER – Installing SQL Server Management Studio 2016 – Step by Step

• 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?

• Thanx a lottt… u saved my day

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

• SURFThru.com
June 10, 2012 1:07 am

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 …

• 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.

• Abhishek Pandey
February 2, 2013 5:11 pm

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………

• 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’

May 26, 2013 8:57 am

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