Random Number on SQL without using NewID()

问题:

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!

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

回答2:

See here: SQL Server - Set based random numbers

回答3:

In SQL Server you can use this to generate a random number, or random integer between the parameters specified

DECLARE @RandomNumber float
DECLARE @RandomInteger int
DECLARE @MaxValue int
DECLARE @MinValue int

SET @MaxValue = 4
SET @MinValue = 2

SELECT @RandomNumber = RAND()

SELECT @RandomInteger = ((@MaxValue + 1) - @MinValue) * @RandomNumber + @MinValue

SELECT @RandomNumber as RandomNumber, @RandomInteger as RandomInteger

回答4:

If you're using MySQL

 SELECT RAND();

MySQL doc: http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_rand

If you're using Postgres

SELECT RANDOM();

Postgres doc: http://www.postgresql.org/docs/8.0/static/functions-math.html

回答5:

Update column with single-digit number

update  [dbo].[AccomodationRatings]
set Rate =rand(CHECKSUM(NEWID()))*10
WHERE Rate >0