SqlServer Rand() question

I am writing a procedure where each call it needs to get a single random number. This procedure is called from our .net web service.

I tried implementing this using rand(). However, when I have multiple calls to the stored procedure within milliseconds, I am getting a lot of collisions in that the same random number is being generated. If there is a space of about 20 or 30 ms between subsequent calls it appears to work ok.

It appears that rand() is reseeded each stored procedure call by SqlServer. From what I understand this is a problem because one should seed a random number generator once and that one doesn't get a good sequence of pseudo-random numbers if one is reseeding each call to rand. Also, it appears that calls to the same sp that are within 1 or 2 milliseconds get seeded with the same value.

Here is the statement itself in the stored procedure.

DECLARE @randomNumber char(9)

SET @randomNumber = RIGHT('00000' + CAST(CAST(rand()*100000 AS INT) AS VARCHAR(5)),5)
+ RIGHT('00000' + CAST(CAST(rand()*10000 AS INT) AS VARCHAR(4)),4)

Does anyone have a suggestion for fixing this?

Will I have to write my own random number generator that is seeded once and saves its state in a table across calls? How does SQL Server seed rand()? Is it truly rando开发者_运维知识库m or if you call an sp within 1 or 2 milliseconds of each other on separate connections will it be seeded with the same seed causing a collision?

If you are using SQL Server 2008, then you can use the CRYPT_GEN_RANDOM() function. This will randomize data for every row even if you were trying to calculate millions of random numbers in one query execution and doesn't have any seeding issues:

SELECT CAST(RIGHT(CAST(CAST(CRYPT_GEN_RANDOM(1) AS INT) AS VARCHAR(100)), 1) AS INT)

Here's the link to the BOL article:

http://msdn.microsoft.com/en-us/library/cc627408.aspx

In your example, replace rand()*10000 with ABS(CHECKSUM(NEWID())) % 9999

However, for char(9):

SELECT RIGHT('000000000' + CAST(ABS(CHECKSUM(NEWID()) % 999999999) AS char(9), 9)

To seed RAND randomly...

RAND(CHECKSUM(NEWID()))

Edit:

Note, RAND is badly implemented in SQL Server. Don't use it.

You could use a table with just an identifier field for creating unique nunbers to use as seed:

declare
  @randomNumber char(9),
  @seed1 int,
  @seed2 int

insert into SeedTable () values ()
set @seed1 = scope_identity()

insert into SeedTable () values ()
set @seed2 = scope_identity()

set @randomNumber = right('00000' + 
    cast(cast(rand(@seed1) * 100000 as int) as varchar(5)), 5) +
    right('00000' + 
    cast(cast(rand(@seed2) * 10000 as int) as varchar(4)), 4)

if (@seed2 > 10000) truncate table SeedTable

The RAND() function has an optional seed parameter that you could use for this. If you pass the last generated random value as a seed to the next call to rand(), you are guaranteed to get a new random number.

Thanks to gbn for pointing out that the seed is an integer, while rand() returns a float. With that knowledge, here's a working example! First create a table:

create table RandomNumber (number float)
insert into RandomNumber values (rand())

Then grab a random number and store the new number in a transaction:

declare @new float
begin transaction
select @new = rand(-2147483648 + 4294967295 * number)
    from RandomNumber with (updlock, holdlock)
update RandomNumber set number = @new
commit transaction
print 'Next bingo number is: ' + cast(cast(@new*100 as int) as varchar)

An SQL Server integer varies between -2147483648 and 2147483647, and a random number is a float between 0.0 and 1.0. So -2147483648 + 4294967295 * number should cover the full range of available integers.

The transaction ensures that only one connection at a time reads and stores a new number. So the numbers are random even on different connections to SQL Server. (By the way, I voted for gbn's answer, seems much easier.)