Random SQL number without using NewID ()

Question

Hello, I want to generate a unique random number with the following statement:

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

The reason when I use joins clauses on "from" generates double registers using NEWID ()

Im using SQL Server 2000

* PD: When I use Rand (), it probably repeats the probability of 1 100000000, but it is so critical, therefore, to repeat the generated random value

there should be a 0% probability

My query with NewID () and the result in the SELECT statement is duplicated (x2) My query without newID () and the use of RAND () in the SELECT statement is one (x1), but the probability of the generated random value being repeated is undefined, but exists!

Thank!

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 a 0% chance of duplicating a number

CHECKSUM (NEWID ())) returns an integer that has 4 billion rows. The paradox means that the probability of a collision is much higher, of course.

Bigint (higher) or decimal (38.0) give you much more opportunities for the game, but only reduce the likelihood of a collision, but are never eliminated.

But do not understand why you are trying to join a unique random number ...

Answer 2

See here: SQL Server - a set of random numbers

Answer 3

SQL Server

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

Answer 4

MySQL

 SELECT RAND();

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

If you are using Postgres

SELECT RANDOM();

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

Answer 5

Refresh Single Digit Column

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