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 Cursor, SQL Random, SQL Scripts, SQL Server, SQL Stored Procedure, SQL Utility
Previous Post
SQL SERVER – Explanation SQL SERVER Merge Join
Next Post
SQL SERVER – 2005 Security DataSheet

Related Posts

113 Comments. Leave new

  • 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

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

    Reply
  • thx, nice info.

    Reply
  • Do you know how the RAND function is implemented? What underlying algorithm is being used?

    Thx.

    Reply
  • 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.
    please help me

    Thanks pete

    Reply
    • This code will do what you needed

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

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

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

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

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

    Reply
  • Best site ever!

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

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

    Random from 1 to 7

    Reply
  • Muhammad Adeel
    February 6, 2011 1:40 am

    Thanks Man, your post helped me so much

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

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

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

      Reply
  • Thanks Madhivanan:
    are you sure this working?
    Consider table name accounts
    and columns name account_id

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

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

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

    Reply
  • The chance of getting random integer number is not equal by using ROUND function. I guess FLOOR or CEILING is better choice –

    SELECT @Random = FLOOR(@Lower + RAND() * (@Upper – @Lower + 1))

    Reply
  • Hi,
    Pinal Dave

    i had a problem i want to create 6 digit non repeating random number like 123456 if 122345 (wrong digits)
    how can i code it

    Reply

Leave a Reply

Menu
Exit mobile version