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.
- Copy Database – SQL in Sixty Seconds #169
- 9 SQL SERVER Performance Tuning Tips – SQL in Sixty Seconds #168
- Excel – Sum vs SubTotal – SQL in Sixty Seconds #167
- 3 Ways to Configure MAXDOP – SQL in Sixty Seconds #166
- Get Memory Details – SQL in Sixty Seconds #165
- Get CPU Details – SQL in Sixty Seconds #164
- Shutdown SQL Server Via T-SQL – SQL in Sixty Seconds #163
- SQL Server on Linux – SQL in Sixty Seconds 162
- Query Ignoring CPU Threads – SQL in Sixty Seconds 161
- Bitwise Puzzle – SQL in Sixty Seconds 160
- Find Expensive Queries – SQL in Sixty Seconds #159
- Case-Sensitive Search – SQL in Sixty Seconds #158
- Wait Stats for Performance – SQL in Sixty Seconds #157
- Multiple Backup Copies Stripped – SQL in Sixty Seconds #156
Reference:Â Pinal Dave (http://blog.SQLAuthority.com)
113 Comments. Leave new
hi
I want to select a four different random number from the four different column of the table and put in in the another column of the table. can any one help me to solve this.
bye
The crucial trick is to add 0.5 (!)
In this way we get no bias against returning the upper and the lower limits
CREATE PROCEDURE GetRandomInteger
(
@Min int,
@Max int,
@rv int OUTPUT
)
AS
BEGIN
DECLARE @Rand float
SELECT @Rand = ((@Max – @Min + 1) * RAND(CONVERT(int, CONVERT(varbinary, NEWID())))) + .5 + (@Min – 1)
SELECT @rv = ROUND(@Rand, 0)
END
I have 400K records on my table. Now I wanted you pull out random records that falls every 5th records from top to bottom. How can I do that sql?
I tried using select stmt (below) but I had message error on LIMIT function.
Select stmt:
select * from table_name order by RAND() limit 5
error msg:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘limit’.
@DM
LIMIT is not ANSI SQL. Not Especially T-SQL . LIMIT Key word is only in MYSQL.
~ IM.
Neat web site – Will definitely visit again!
Hi – loved this article. Got an interesting twist, though. Regarding ‘Method 5 : Random number on a per row basis’ – if I do this:
SELECT ABS(CAST(NEWID() AS binary(6)) %3)
I will always get either a 0, 1, or 2 – perfect!
However, what I am ultimately trying to do is to assign a mapped value to a field depending on the value of the random number generated above, like this:
UPDATE MyTable
SET MyField =
CASE ABS(CAST(NEWID() AS binary(6)) % 3)
WHEN 0 THEN ‘red’
WHEN 1 THEN ‘blue’
WHEN 2 THEN ‘green’
END
WHERE KeyField = ‘somevalue’
Apparently the CASE statement is getting confused at times, and although in most cases, the field will be be updated with ‘red,’ ‘blue,’ or ‘green,’ at least 30% of the time, the CASE statement returns NULL! I don’t know why this would possibly be happening, since using ABS(CAST(NEWID() AS binary(6)) % 3) without a CASE statement NEVER returns NULL!
To test this yourself, first trying executing this several times:
SELECT ABS(CAST(NEWID() AS binary(6)) % 3)
You’ll see that you never get a NULL returned.
Next, try executing this several times:
SELECT CASE ABS(CAST(NEWID() AS binary(6)) % 3)
WHEN 0 THEN ‘zero’
WHEN 1 THEN ‘one’
WHEN 2 THEN ‘two’
END
You’ll many times that you get a NULL returned.
HELP!
Thanks pinal,
You are the best.
Great lesson but how can I display all of these in one record?
Great help this will come in really handy for my latest application
Love the blog.
Hi
I have table with 100 records, how can I update a field on this with random numbers from 1 thru 100, when I do this using RANd() I do get duplicate random numers in the file which does not help me. Any help?
If you want each of the 100 records to have a unique number, just assigned in a random order, don’t use the Rand function.
Use the ROW_NUMBER() and NEWID() functions… Like this.
UPDATE t1 SET Random = t2.RN
FROM TableName AS t1
INNER JOIN (
SELECT RowID, ROW_NUMBER() OVER(ORDER BY NEWID())AS RN FROM TableName) AS t2 ON t1.RowID = t2.RowID
Hello.
I think the first script, is flawed.
This one:
—- 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
If you set a lower limit of 1, and upper limit 2, it will always return one.
If you remove the ‘-1’ from here: ROUND(((@Upper – @Lower -1) * RAND() + @Lower), 0) it returns 1 and 2.
Maybe I am mistaken and this is a particular case.
Thanks for the great site, I found many useful scripts here. :)
Regards,
Alex Luca
Actually you are correct. The 1st one is flawed as it’s written. It will in fact cut off the @Upper value. Also it fails to provide an even distribution of occurrences with the 1st and last numbers within the range.
The following fixes both issues.
DECLARE @Rand1 INT;
DECLARE @Random INT;
DECLARE @Upper INT;
DECLARE @Lower INT
SET @Lower = 5 —- The lowest random number
SET @Upper = 55 —- The highest random number
SET @Rand1 = ROUND((((@Upper + 1) – @Lower) * RAND() + @Lower), 0)
SET @Random = CASE WHEN @Rand1 = @Upper + 1 THEN @Lower ELSE @Rand1 END
Cheers,
Jason
suppose i wan a random number between 1,2,3,4,5,12,13,34,23
dis set of numbers den how do i do dat.????
Pls help
@sharry
Use RAND() to get a number between 1 and 9, and use the number as an index into your number string.
If the number is given as a long string with varying lengths, you may need to expand the list first. There is a good article on how to do that here:
Great article. What I need is a way to product int char char int. Example: 0AA0 – Position 1 – a number between 1-9, Position 2 – a letter between A-Z, Position 3 – a letter between A-Z, Position 4 – a number between 1-9.
Very new to SQL, T-SQL – so be kind. :-)
You can solve random string generation using a mask and substitution. There are many ways to solve this issue, but if we break them down into 3 parts, it will make our solution more flexible.
Part 1 – The randomizer view, this is nothing new, just generating a random float in a view so it can be used in a function.
CREATE VIEW [dbo].[Randomizer] AS
SELECT abs((convert(bigint,convert(binary(8),newid()))*0.000000000000001)%1.0) AS n
Part 2 – A Random function that takes min and max parameters so that we can generate a random value within desired bounds.
CREATE FUNCTION dbo.Rnd( @min bigint, @max bigint ) RETURNS bigint AS
BEGIN
RETURN convert(bigint,
(@max – @min + 1)
*
(SELECT TOP 1 n FROM dbo.Randomizer)
+
@min
);
END
Part 3 – A generic mask substitution function so that we can ask specify any mask we want. I will only implement the very most basic set of substitutions. You can extend these in simple ways.
CREATE FUNCTION dbo.RandomText( @mask varchar(64) ) RETURNS varchar(64) AS
BEGIN
— Goal: Substatute mask tokens with random values
— Inputs: @mask – a string with subtatution tokens and literals
— Tokens: # – will be replaced with a random digit [0-9]
— A – will be replaced with a random upper case english letter [A-Z]
SELECT
@mask = Stuff( @mask, start, 1, replacement )
FROM (
SELECT
n AS start,
CASE SubString( @mask, n, 1 )
WHEN ‘#’ THEN char( dbo.Rnd(48,57) ) — [0-9]
WHEN ‘A’ THEN char( dbo.Rnd(65,90) ) — [A-Z]
END AS replacement
FROM (
SELECT (1+n1.n+n10.n) AS n
FROM
(SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) n1
CROSS JOIN (SELECT 0 AS n UNION SELECT 10 UNION SELECT 20 UNION SELECT 30 UNION SELECT 40 UNION SELECT 50 UNION SELECT 60) AS n10
WHERE
(1+n1.n+n10.n) BETWEEN 1 AND Len(@mask)
) sequence
WHERE
SubString( @mask, n, 1 ) IN (‘#’,’A’)
) substatutions
RETURN @mask
END
Additionally you can replace the use of the Char() function with a SubString.
Example
SubString(‘0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ’, dbo.Rnd(1,36), 1)
For a random digit or letter and that list could obviously be extended to include any characters you wish.
Perfect!
Hi Dave,
Is there a possibility to find a null present in table as there are many columns in my table is there any other possible way to find the null in my columns in my table.
Eg. SLNO, Name, DOB,…….n’ column i have i can use isnull but i have to mention each and every column name in the isnull function i guess its pain taking process. so i need to know is there any possible way through which i can find that null values is present in my table. using a query.
Awaiting for you reply.
Cheers …
hi Pinal,
My qusetion is like sometimes there is an requirement to genearete the random integers numbers from 1 to 10 like.
If i m creating the online exam application and want to generate the 5 random numbers between 1 to 10 so that only thoese question numbers can be displayed at a time.
I have seen such cases where the random numbers my be also duplicate like 1 and again one and the processs is alos recurssive so it hangs the application and fails or stack over flow problem.
i do not have the code snipet now but can u give som e solution for this problem with code.
Thanks
to Archana:
SELECT TOP 5 *
FROM testqs
ORDER BY newid()
You can also make use of CHECKSUM to get randome numbers.
Refer this for more informations