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
Reference : Pinal Dave (http://blog.SQLAuthority.com), Simon Sabin (http://sqlblogcasts.com)
pinaldave
Good info, it helped me understand sql rand function better.
However I don’t seem to find an answer to what I’m trying to do.
I have a table that looks like this
Var Number
ff 0
zap 0
zf 0
and so on…
What I want to do is assign a random int positive number for each Number between a certain range.
Is that possible?
Thanks
Hi!
Do you know how to create a random
XX-###
XX are character rand()
### number [0 to 9] rand()
I need to produce or generate at least the first 100 to 1000 count() and more later
thanks
Vince
Hi,
If I need to check the possibilities of numbers generated within given range. if I wanna generate alphanumeric 4 charater values like A2B8,A3A2,..etc. How I find out the number of possibilities
Thanks,
Damith
Great article. I am a noob to sql and know it works but not quite sure. In regards to this bit.
– This will create a random number between 1000 and 9999
SET @Lower = 1000 — The lowest random number
SET @Upper = 9999 — The highest random number
SELECT @Random = Round(((@Upper – @Lower -1) * Rand() + @Lower), 0)
SELECT @Random
I am having a hard time understanding how this works. If I replace the upper with 9999 and the lower with 1000 I get (8998) * Rand() + 1000), 0
This is the section I am not understanding how it would generate a 4 digit random number.
Hi,
I want to select top random generated 3 records from top 30 records order by some column name with out using temporary table or variable.Is it possible?
Thanks in advance.
Teena
Hi Teena,
Try this
SELECT TOP 3 * FROM employees ORDER BY 1
In the random number generator between the range the higher end number, 999. Will never be generated. I tried this by lowering the scope to 1 as low 3 as hi and never got 3 i would get lots of 1’s and 2’s but no 3. So if you want to get the top number you need to add 1 to it to include it within the range.
Hi Pinal,
I am working as a MSSQL as well as aOracle DBA. I have been looking for health check scripts on SQL Server for quite some time. Please advice where I can get good health check scripts
You are the good!!
I would like to find very least missed number from the table. I have a empID field in Employee table. The data would be like this..
empId
0
3
5
6
From above data, the least missed number is: 1
So my query should return a value : 1
How to do this.
Hope you understand my question..
Further to Michael’s comments, also be careful when using method 1 if you require equal probability, as for ranges with a difference of 3 or more the first and last numbers in the range are only c. half as likely to be generated as the ‘middle’ numbers, e.g. a sample of 10000 nos from 1 to 9 (ie @lower=1, @upper = 10):
val: Occurrence (sample):
1 610
2 1296
3 1299
4 1275
5 1244
6 1225
7 1212
8 1235
9 604
It can be difficult to get a random integer within a specified range, with a good quality probability distribution.
The following gives a pretty good distribution, plus is flexible when it comes to specifying the range.
DECLARE @Rand SMALLINT
DECLARE @UpperLimit SMALLINT
DECLARE @LowerLimit SMALLINT
SET @UpperLimit = 1
SET @LowerLimit = -1
SET @Rand =
@LowerLimit+
ABS((CAST(NEWID() AS VARBINARY))%
(1+@UpperLimit-@LowerLimit))
SELECT @Rand
Both the upper and lower limit values are returned. Negative ranges can be specified, but the @UpperLimit must always be greater than the @LowerLimit.
It can easily be made into a UDF or SP.
Please send me some script to t
hi there…. thanks for the sharing, anyway I have this problem regarding to my objective in simulation (markov chain monte carlo). because it seems that every time I “run” the query, the random number seems to be differ than the latest one. Its giving me a hard time in analyzing the system that I simulate.
Is there any possibility to randomize fixed number…..
for example….
1st run (range 1′ cut – 9′ cut)
5*,6,7,8,3,4,2,5,6
2nd run (range 1′ cut – 9′ cut)
5*,6,7,8,3,4,2,5,6
3rd run (range 2′ cut – 10′ cut)
6*,7,8,3,4,2,5,6,8*
please reply as soon as possible.
thanks for your information and sharing.
To Sundar; a quick, dirty way of doing what you want would be something like…
SELECT MIN(empID) + 1 AS lowestMissedValue
FROM Employee
WHERE ((SELECT TOP 1 (empID + 2)
FROM Employee E
WHERE E.empID = Employee.empID) Employee.empID))
It’ll only find values that are missing above the minimum, though, so if your lowest value is 2000, then the first value it could return is 2001.
Damn, it cut out part of the SQL. Basically, you’re retrieving the minimum value plus one, for values where the same minimum value plus TWO is less than or equal to the next highest value, meaning that there’s a gap of at least two numbers between that value and the one following it. You should be able to fill in the SQL gaps from that.
Thank you for your great articule!
Hi, I have a data table , in which i want only selected (4) records when i press next button the next set of selected (4) records should be displayed like paging option in grid view .. is there any query to perform this operation.. so that loading the entire table in to grid can be avoided… please give your comments… thanks in advance
I have a stored procedure that selects a number of rows dependant on various input values –
for example, say the query selects 12 different rows, how can I then rendomly select one of the twelve and drop the other 11?
thankyou
Matt
Thank you … saved my time
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
[...] SQL SERVER – Random Number Generator Script – SQL Query [...]
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?
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
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: http://bradsruminations.blogspot.com/2009/10/un-making-list-or-shredding-of-evidence.html
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.