Random Number on SQL without using NewID()

Question

Hello I want to generate a Unique Random number with out using the follow statement :

Convert(int, (CHECKSUM(NEWID()))*100000) AS [ITEM] 

Cause when I use joins clauses on "from" it generates double registers by using NEWID()

Im using SQL Server 2000

*PD : When I use Rand() it probably repeat on probability 1 of 100000000 but this is so criticall so it have to be 0% of probability to repeat a random value generated

My Query with NewID() and result on SELECT statement is duplicated (x2) My QUery without NewID() and using Rand() on SELECT statement is single (x1) but the probability of repeat the random value generated is uncertainly but exists!

Thanks!

Answer 1

Is it overflowing?

CAST(CHECKSUM(NEWID()) AS bigint) * CAST(100000 AS bigint) AS [ITEM]

CAST(CAST(CHECKSUM(NEWID()) AS bigint) * CAST(100000 AS bigint) % 2100000000 AS int) AS [ITEM]

Edit:

There is no such thing as 0% chance of duplicated number

CHECKSUM(NEWID())) returns an integer, which has 4 billion rows. The birthday paradox means the chance of collision is much higher of course.

Bigint (above) or decimal(38,0) give you a lot more room to play with but only reduce the chance of collision but never eliminate.

But still don't get why you're trying to JOIN in a unique randomnumber...

Answer 2

See here: SQL Server - Set based random numbers