# Generate unique random number in sql server 2005... [Answered]RSS

## 7 replies

Last post Apr 18, 2012 11:37 AM by abiruban

Member

113 Points

358 Posts

### Generate unique random number in sql server 2005...

Apr 12, 2012 06:43 AM|ROHIT SOOD|LINK

<div>

Hi every1

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.

Thanks

Rohit

</div>

All-Star

78951 Points

16383 Posts

### Re: Generate unique random number in sql server 2005...

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

Thanks,

Member

160 Points

75 Posts

### Re: Generate unique random number in sql server 2005...

Apr 12, 2012 08:51 AM|umarii090|LINK

http://weblogs.sqlteam.com/jeffs/archive/2004/11/22/2927.aspx

Participant

1293 Points

556 Posts

### Re: Generate unique random number in sql server 2005...

Apr 12, 2012 09:01 AM|Mandeep Joon|LINK

Hi,

you can give a try to identity .

(id int identity(10000000 ,189) primary key,

Mark this as answer if it is usefull.

Mark as answer if it helped you
Mandeep Joon
Blog:- Hightechnology.

Member

71 Points

41 Posts

### Re: Generate unique random number in sql server 2005...

Apr 12, 2012 09:19 AM|rupeshkura|LINK

Hai,

Please See the Below Links It Will be helpful.................

http://blog.sqlauthority.com/2007/04/29/sql-server-random-number-generator-script-sql-query/

http://stackoverflow.com/questions/94906/how-do-i-return-random-numbers-as-a-column-in-sql-server-2005

Thanks,

http://rupeshkura.blogspot.in

Member

113 Points

358 Posts

### Re: Generate unique random number in sql server 2005...

Apr 12, 2012 09:37 AM|ROHIT SOOD|LINK

### ramiramilu sir

could you frame me the sample working structure on how to do

this approach in a best way...

Contributor

2950 Points

423 Posts

### Re: Generate unique random number in sql server 2005...

Apr 18, 2012 10:19 AM|Richey|LINK

Star

11260 Points

2685 Posts

### Re: Generate unique random number in sql server 2005...

Apr 18, 2012 11:37 AM|abiruban|LINK

Hi,

Here is a way in SQL to generate Random, Unique and Reliable Alpha Numeric Values..

<div id="codeSnippetWrapper"> <div id="codeSnippet">
`SELECT  NEWID()`

</div> </div>

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

<div id="codeSnippetWrapper"> <div id="codeSnippet">
```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```

</div> </div>

and here is the Script for generating 8 characters Alpha Numeric values .

<div id="codeSnippetWrapper"> <div id="codeSnippet">
```DECLARE @alpha_numeric VARCHAR(8)
SET @alpha_numeric=''
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

SELECT @alpha_numeric

```

</div> </div>

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.

Thanks…