Last post Apr 18, 2012 11:37 AM by abiruban
Apr 12, 2012 06:43 AM|ROHIT SOOD|LINK
i want to generate 10 random numbers betweeen 1 to 50 in sql server 2005, and every randomly generated number should be unique....
means no number should be repeated again till 10th no.
Iam using this code to generate random numbers from 1 to 50 but they are not coming unique,sometimes same number is coming twice...
DECLARE @rand INT;
DECLARE @Upper INT;
DECLARE @Lower INT;
---- This will create a random number between 1 and 50
SET @Lower = 1 ---- The lowest random number
SET @Upper = 50 ---- The highest random number
SELECT @rand = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
-- print @rand
Plz give some suggestions...either in this code or some other way...Any help will be appreciated.
Apr 12, 2012 08:48 AM|ramiramilu|LINK
simple solution would be to use a temptable and store a random number in there, and when next random number has been generated then compare it with already existing values in temptable, if it already exisits then generate one more, or else store this number
again in temptable...continue this for all random numbers...
NOTE - This approach might not be the best way to solve problem...
Apr 12, 2012 08:51 AM|umarii090|LINK
Apr 12, 2012 09:01 AM|Mandeep Joon|LINK
you can give a try to identity .
(id int identity(10000000 ,189) primary key,
Mark this as answer if it is usefull.
Apr 12, 2012 09:19 AM|rupeshkura|LINK
Please See the Below Links It Will be helpful.................
Apr 12, 2012 09:37 AM|ROHIT SOOD|LINK
could you frame me the sample working structure on how to do
this approach in a best way...
Apr 18, 2012 10:19 AM|Richey|LINK
You can refer to the below link about generate random number in Sql Server.
You can also post the question in the SQL server forum.
Apr 18, 2012 11:37 AM|abiruban|LINK
Here is a way in SQL to generate Random, Unique and Reliable Alpha Numeric Values..
This always give you unique, random values but as the length of the data as a result of this is too large so its not easy to remember that.
So if you want to Generate 8 characters Numeric or Alpha Numeric values then here is the script for that.
By this script you generate 8 characters Numeric values
SELECT number/nullif(power(10,len(number)-8),0) AS number FROM
SELECT TOP 1 ABS(CHECKSUM(NEWID())) AS number
FROM sysobjects s1 cross join sysobjects s2
) AS T
and here is the Script for generating 8 characters Alpha Numeric values .
DECLARE @alpha_numeric VARCHAR(8)
SELECT @alpha_numeric=@alpha_numeric+CHAR(n) FROM
SELECT TOP 8 number AS n FROM master..spt_values
WHERE TYPE='p' and (number between 48 and 57 or number between 65 and 90)
ORDER BY NEWID()
) AS t
So in this way you generate 8 characters Numeric and Alpha Numeric values…
You can also change the length of your String by Alter some values in above script.