# 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 (http://blog.SQLAuthority.com), Simon Sabin (http://sqlblogcasts.com)

## 107 thoughts on “SQL SERVER – Random Number Generator Script – SQL Query”

1. Daniel |

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

Like

2. Vince |

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

Like

3. Damith |

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

Like

• Been there done that Damith. You’re likely referring to Base N conversion from Base 10 (Decimal System). If your alpha characters are only A’s and B’s, then you’re dealing with Base 12 numbering system. Your number of possibilites lays in the limit of the length of the string and the Base 10 number you are ranging in your random function.

Look at base number conversion: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=109916

Like

4. Richard |

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.

Like

5. Teena |

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

Like

6. Hi Teena,

Try this

SELECT TOP 3 * FROM employees ORDER BY 1

Like

7. Michael |

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.

Like

• An easy change to Method 1 to make it more accurate is to replace the ROUND line as follows:

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

The entire method would then read:

DECLARE @Random INT
DECLARE @Upper INT
DECLARE @Lower INT

SET @Lower = 0 —- The lowest random number
SET @Upper = 14 —- The highest random number
SELECT @Random = FLOOR(((@Upper – @Lower + .9999999999) * RAND() + @Lower))
SELECT @Random

Like

8. John |

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

Like

9. Nirondes |

You are the good!!

Like

10. Sundar |

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

Like

• Do you have any unique/primary key in the table?

Like

11. Andy |

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

Like

12. Em |

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.

Like

Please send me some script to t

Like

14. suryo_indonesia |

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*

thanks for your information and sharing.

Like

15. Deacon |

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.

Like

16. Deacon |

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.

Like

17. Alfredo Barrios |

Thank you for your great articule!

Like

18. Nagarajan |

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

Like

19. Matt |

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

Like

20. Vaishali R |

Thank you … saved my time

Like

21. pranay |

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

Like

22. Simon |

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

Like

23. DM |

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’.

Like

24. Imran Mohammed |

@DM

LIMIT is not ANSI SQL. Not Especially T-SQL . LIMIT Key word is only in MYSQL.

~ IM.

Like

25. CFLisa |

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!

Like

26. New techie Praveen |

Thanks pinal,

You are the best.

Like

27. Phoeneous |

Great lesson but how can I display all of these in one record?

```DECLARE @Random_Sales INT;
DECLARE @Upper_Sales INT;
DECLARE @Lower_Sales INT;
DECLARE @Random_Profit INT;
DECLARE @Upper_Profit INT;
DECLARE @Lower_Profit INT;
DECLARE @Random_Percent INT;
DECLARE @Upper_Percent INT;
DECLARE @Lower_Percent INT

SET @Lower_Sales = 500000
SET @Upper_Sales = 599999
SET @Lower_Profit = 60000
SET @Upper_Profit = 99999
SET @Lower_Percent = 15
SET @Upper_Percent = 20

SELECT @Random_Sales = ROUND(((@Upper_Sales - @Lower_Sales -1) * RAND() + @Lower_Sales), 0)
SELECT @Random_Sales
SELECT @Random_Sales = ROUND(((@Upper_Sales - @Lower_Sales -1) * RAND() + @Lower_Sales), 0)
SELECT @Random_Sales
SELECT @Random_Profit = ROUND(((@Upper_Profit - @Lower_Profit -1) * RAND() + @Lower_Profit), 0)
SELECT @Random_Profit
SELECT @Random_Percent = ROUND(((@Upper_Percent - @Lower_Percent -1) * RAND() + @Lower_Percent), 0)
SELECT @Random_Percent
```

Like

28. Great help this will come in really handy for my latest application

Love the blog.

Like

29. Tejpal |

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?

Like

• Jason Long |

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

Like

30. 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

Like

• Jason Long |

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

Like

31. sharry |

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

Like

32. John |

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. :-)

Like

33. Dennis A |

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
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
) sequence
WHERE
SubString( @mask, n, 1 ) IN (‘#’,’A’)
) substatutions

END

Like

34. Dennis A |

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.

Like

35. David |

Perfect!

Like

36. Narasimhan |

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.

Cheers …

Like

37. Archana |

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

Like

• LP0rc |

to Archana:

SELECT TOP 5 *
FROM testqs
ORDER BY newid()

Like

38. mohan |

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

Like

• You can do it like this.
select top 2 locationid from profile tablesample(1000)

Like

• Richard Antony |

select Top 2 Userid from [User] tablesample(100)
order by newid()

Like

39. 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

Like

thx, nice info.

Like

41. Do you know how the RAND function is implemented? What underlying algorithm is being used?

Thx.

Like

• It just generates the numbers between 0 and 1. It is similar to the one available in C language

Like

42. peter mann |

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.

Thanks pete

Like

• This code will do what you needed

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

Like

• strategyworks |

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

Like

43. peter mann |

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

Like

• insert into table(decimal_col)
select cast(rand() as decimal(8,6))

Like

44. Brook19 |

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

Like

• Sumit Kumar |

use it
select top 50 ques from questionnaire order by newid()

Like

45. Sumit Kumar |

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

Like

• Check the uniqueness

select ABS(CHECKSUM(NEWID())+999999)%1000000

Like

46. kenji |

Best site ever!

Like

47. ala'a bahar |

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

Like

48. UPDATE seeker_profile SET career_level_id= CEIL(RANDOM()*7);

Random from 1 to 7

Like

Thanks Man, your post helped me so much

Like

50. DANI |

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

Like

51. Peacemaker |

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.

Like

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

Like

52. Peacemaker |

are you sure this working?
Consider table name accounts
and columns name account_id

Like

53. alwaysinmotion |

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

Like

54. will |

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

Like

55. Rajesh |

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

Like

56. Jia |

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

Like

57. amit shukla |

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

Like

58. bud o |

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?

Like

59. idris |

Thanx a lottt… u saved my day

Like

60. Bhushan |

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

Like

61. Your random number using just (SELECT ABS(CAST(NEWID() AS binary(6)) %1000) + 1 ) as RandID beats the RAND() function hands down!!!!

Like

62. matt212 |

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 …

Like

63. Yogi |

This works for me Thanks

Like

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

Like

65. 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………

Like

66. rao mohsin |

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

Like

67. Wasim Khan |

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.

Like

68. Ripal |

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

Like

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

Like

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.

Like

71. ben |

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’

Like

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.

Like

73. Maynard |

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!

Like

74. Bruce Wilson |

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

Like

75. These are greats approaches.. thanks for sharing.

Like

76. Grengas |

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

Like

77. 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]

Like