Sql-server – SqlServer Rand() question

randomsql-server

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 random 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?

Best Solution

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

Related Question