SQL SERVER – Random Number Generator Script – SQL Query

Random Number Generator

There are many methods to generate random number 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 with 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

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , , , ,
Previous Post
SQL SERVER – Replication Keywords Explanation and Basic Terms
Next Post
SQL SERVER – 2005 Security DataSheet

Related Posts

113 Comments. Leave new

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

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

    Reply
  • 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,

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

      Reply
    • 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)

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

    Reply
    • Robert Dannelly
      June 20, 2017 8:39 pm

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

      Reply
  • 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)

    Reply
  • example : i have a table, my table having coloumn a,b,c . coloumn a (Primary key) then coloumn b and c data type is integer, how to create select between numer range cloumn b and c, when them input value random ?

    Reply

Leave a Reply

Menu