8

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!

gbn
  • 422,506
  • 82
  • 585
  • 676
Jonathan Escobedo
  • 3,977
  • 12
  • 69
  • 90
  • What database? Oracle? MSSQL? MySQL? PostgreSQL? Many databases already have random functions. – FrustratedWithFormsDesigner Feb 11 '10 at 20:42
  • What's the problem? SIGN? No floating point? Overflow? There's a reason why we use NEWID() too: it's probably the best solution – gbn Feb 11 '10 at 20:49
  • 1
    @Angel Escobedo, when I run your command: **select Convert(int, (CHECKSUM(NEWID()))*100000) AS [ITEM]** I get: **Msg 8115, Level 16, State 2, Line 1 Arithmetic overflow error converting expression to data type int.** – KM. Feb 11 '10 at 20:51
  • I cannot use NEWID() cause when I use a Join it generated a duplicity also if I use Distinct on SELECT statement – Jonathan Escobedo Feb 11 '10 at 20:56
  • @KM select Convert(int, RAND(CHECKSUM(NEWID()))*10000) AS [ITEM] = 8114 for me SQL Server 8.0 (20000) SP 4 – Jonathan Escobedo Feb 11 '10 at 21:04
  • @angel: just run it a few more times. Any int * 10000 will overflow at some point and CHECKSUM is int already. And where did RAND come into it? – gbn Feb 11 '10 at 21:11
  • @Angel Escobedo, I guess it depends on the value that NEWID() is returning. I ran _CHECKSUM(NEWID())_ ten times here are the results: _-819851858, -2120303339, 1696146315, 1441986231, -2067622907, 1772704734, -1169385946, -1065299277, -615333786, -1210149121_ now try to multiply them by 100000. I don't understand the multiply by 100000, do you just want extra zeros on the end? – KM. Feb 11 '10 at 21:19

6 Answers6

9

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

gbn
  • 422,506
  • 82
  • 585
  • 676
1

See here: SQL Server - Set based random numbers

SQLMenace
  • 132,095
  • 25
  • 206
  • 225
0

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
Simon Mark Smith
  • 1,130
  • 3
  • 13
  • 19
0

Ok, so first if you don't want to use "NEWID" function to generate a random number, then you could use the "RAND" function, something like this would be randomish:

SELECT CAST(ROUND((RAND() - .5) * 2147483647, 0) AS INT)

If you need a BIGINT, cast it to a BIGINT, but keep in mind it's only going to give you the entropy (and Range) of a INT:

SELECT CAST(ROUND((RAND() - .5) * 2147483647, 0) AS BIGINT)

Allot of these other answers are returning BIGINT's for that I would recommend using the "NEWID" function, but I would do this different. For instance, if your doing any variant of using one "CHECKSUM(NEWID()) AS bigint)" and then returning a BIGINT, you only have the entropy of the INT, rather than one of a BIGINT, instead try using two:

SELECT CAST(CAST(CHECKSUM(NEWID()) AS BINARY(4)) + CAST(CHECKSUM(NEWID()) AS BINARY(4)) AS BIGINT)

I suppose if you really don't like "NEWID" function you could do this with "RAND" too:

SELECT CAST(CAST(ROUND((RAND() - .5) * 2147483647, 0) AS BINARY(4)) + CAST(ROUND((RAND() - .5) * 2147483647, 0) AS BINARY(4)) AS BIGINT)

Now that should (more or less) give you the entropy of a BIGINT, of course it's probably not perfect and I haven't proved out yet whether it generates a even distribution, so use with caution.

Really if you want ensure a given ID is unique, you need to use a GUID or a GUID equivalent, if your generated value in not within that range you will have to do that, or track generated fields in a separate table/scan the table before creating the ID. There is not a way around that. Also you cannot generate two independent values in a random way with a 0% chance of collision. You can get close enough to be perfectly safe though.

(Note: For those NOT afraid of using NEWID) I would do the following to get the full 16 bytes of entropy with minimal gymnastics:

SELECT CAST(CAST(NEWID() AS VARBINARY(8)) AS BIGINT)
David Rogers
  • 2,601
  • 4
  • 39
  • 84
-1

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

Daniel
  • 617
  • 1
  • 7
  • 10
-1

Update column with single-digit number

update  [dbo].[AccomodationRatings]
set Rate =rand(CHECKSUM(NEWID()))*10
WHERE Rate >0
Omerović Sanjin
  • 25
  • 1