TechTalkz.com Logo Ask the Experts!

Go Back   TechTalkz.com Technology & Computer Troubleshooting Forums > Tech Support Archives > Microsoft > Microsoft SQL Server

Random ID Number?

Microsoft SQL Server

 
 
Thread Tools Display Modes
Unread 26-10-2007, 01:06 PM   #1
Paddy Mac
Guest
 
Posts: n/a
Random ID Number?

Hi,

Is there a way to setup a random number generator in a SQL table? Say I want a 10 digit number which will be a unique reference number, and be randomly generated, how would I do this? I've only done increments in SQL.

Thanks for your advice!!

Paddy


EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com

Sponsored Links
 
Unread 26-10-2007, 02:06 PM   #2
Razvan Socol
Guest
 
Posts: n/a
Re: Random ID Number?

Paddy Mac wrote:
> Is there a way to setup a random number generator in a SQL table?
> Say I want a 10 digit number which will be a unique reference number,
> and be randomly generated, how would I do this? I've only done
> increments in SQL.


Here is something fairly simple:

SELECT REPLACE(STR(CAST(CAST(NEWID() AS varbinary(4)) AS int),10),' ',0)

However it only generates numbers between 0000000000 and 4294967295.

--
Razvan Socol
SQL Server MVP
 
Unread 26-10-2007, 02:06 PM   #3
Alex Kuznetsov
Guest
 
Posts: n/a
Re: Random ID Number?

On Oct 26, 8:01 am, Paddy Mac wrote:
> Hi,
>
> Is there a way to setup a random number generator in a SQL table? Say I want a 10 digit number which will be a unique reference number, and be randomly generated, how would I do this? I've only done increments in SQL.
>
> Thanks for your advice!!
>
> Paddy
>
> EggHeadCafe - .NET Developer Portal of Choicehttp://www.eggheadcafe.com


random number generators do not guarantee uniqueness, although the
probability of a collision may be low.

 
Unread 26-10-2007, 02:06 PM   #4
Razvan Socol
Guest
 
Posts: n/a
Re: Random ID Number?

Razvan Socol wrote:
> SELECT REPLACE(STR(CAST(CAST(NEWID() AS varbinary(4)) AS int),10),' ',0)


Correction:

SELECT REPLACE(STR(CAST(CAST(NEWID() AS binary(4)) AS bigint),10),' ',0)

--
Razvan Socol
SQL Server MVP
 
Unread 29-10-2007, 03:07 PM   #5
Paddy Mac
Guest
 
Posts: n/a
Thanks

Thanks a lot Razvan! that did the trick!!

EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com
 
Unread 29-10-2007, 05:12 PM   #6
ML
Guest
 
Posts: n/a
RE: Thanks

Sponsored Links
Add a trigger to prevent duplicates and voila! You've got one heck of a
bottleneck.


ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/

Sponsored Links
 
 

Thread Tools
Display Modes



< Home - Windows Help - MS Office Help - Hardware Support >


New To Site? Need Help?

All times are GMT. The time now is 10:09 PM.


vBulletin, Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright © 2005-2016, TechTalkz.com. All Rights Reserved - Privacy Policy
Valid XHTML 1.0 Transitional