# 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)

#### Related Posts

• SHUBHAM SAXENA
May 21, 2014 4:18 pm

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]

• Haris
June 18, 2016 3:38 am

Do it this way:
SELECT FLOOR(RAND() * POWER(CAST(10 as BIGINT), 4)) — 4 is the number of digits required in the random number.

• November 29, 2016 12:09 pm

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,

• Ashok
March 27, 2017 7:14 pm

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

• nanonerd
March 30, 2018 8:09 pm

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)

• Pinal Dave
April 2, 2018 4:21 am

I realized that RAND doesn’t work the way I thought.

• Robert Dannelly
June 20, 2017 8:24 pm

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

• Robert Dannelly
June 20, 2017 8:39 pm

Got it – select CONVERT(numeric(22,0),RAND() * 10000000000000000000000) + CONVERT(numeric(22,0),RAND() * 100000)

• vishnuvardhan
August 17, 2018 3:39 pm

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)

• 