# 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 – AlwaysOn Listener Error – The WSFC Cluster Could Not Bring the Network Name Resource With DNS Name ‘DNS name’ Online

• 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

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

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

Thanks pete

• This code will do what you needed

select cast(rand() as decimal(8,6))

• strategyworks
June 3, 2014 1:49 am

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

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

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

• Best site ever!

• hello i need a procedure to insert a random value and select the this randomly

• UPDATE seeker_profile SET career_level_id= CEIL(RANDOM()*7);

Random from 1 to 7

February 6, 2011 1:40 am

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 )

are you sure this working?
Consider table name accounts
and columns name account_id

• alwaysinmotion
October 5, 2011 5:58 pm

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