SQL SERVER – Random Number Generator Script – SQL Query

Random Number Generator

There are many methods to generate random numbers in SQL Server.

SQL SERVER - Random Number Generator Script - SQL Query RandomNumberGenerator-800x533

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)

, , , , ,
Previous Post
SQL SERVER – Explanation SQL SERVER Merge Join
Next Post
SQL SERVER – 2005 Security DataSheet

Related Posts

113 Comments. Leave new

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

    Reply
  • Thanx a lottt… u saved my day

    Reply
  • Please help me for below qery , I m checking series between two number
    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

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

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

    Please Help!

    Reply
  • This is the best alternative to NewId() I found. NewId() has performance issues when it comes to large record sets.

    Reply
  • This works for me Thanks

    Reply
  • Simple, effective – quite nearly poetic Pinal! Love it.

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

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

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

    Reply
  • Hi
    How to auto generate number between 10000 to 99999 without repeat number in sql server

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

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

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

    Please help.

    Reply
  • Ramesh Rabadiya
    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.

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

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

    Reply
  • These are greats approaches.. thanks for sharing.

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

    Reply

Leave a Reply

Menu