SqlServer Rand () question
I am writing a procedure where each call needs to get one random number. This procedure is called from our .net web service.
I tried to implement this using rand (). However, when I have multiple calls to the stored procedure in milliseconds, I get a lot of collisions in that the same random number is generated. If there is a gap of about 20 or 30ms between subsequent calls, it works fine.
It looks like rand () reloads every call to the SqlServer stored procedure. From what I understand, this is a problem because the random number generator needs to be generated once, and that a good pseudo-random number sequence does not work out if rewriting every call of rand every time. Also, it looks like calls to the same sp that are within 1 or 2 milliseconds are seeded with the same value.
Here is the instruction 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 to fix this?
Should I write my own random number generator that is seeded once and stores its state in the table across calls? How does SQL Server seed rand ()? Is this really a random case, or if you call sp within 1 or 2 milliseconds of each other on separate connections, will it be seeded with the same seed causing the collision?
If you are using SQL Server 2008, you can use the CRYPT_GEN_RANDOM () function. This will randomize the data for each row, even if you tried to compute millions of random numbers in a single query execution and have no seeding issues:
SELECT CAST(RIGHT(CAST(CAST(CRYPT_GEN_RANDOM(1) AS INT) AS VARCHAR(100)), 1) AS INT)
Here's a link to the BOL article:
source to share
In your example, replace rand()*10000
withABS(CHECKSUM(NEWID())) % 9999
However, for char (9):
SELECT RIGHT('000000000' + CAST(ABS(CHECKSUM(NEWID()) % 999999999) AS char(9), 9)
To accidentally crash RAND ...
RAND(CHECKSUM(NEWID()))
Edit:
Note. RAND is poorly implemented in SQL Server. Don't use it.
source to share
The RAND () function has an optional seed parameter that you could use to do this. If you pass the last generated random value as the seed to the next rand () call, you are guaranteed to get a new random number.
Thanks to gbn for indicating that the seed is an integer and rand () returns a float. With this knowledge, here's a working example! First, create a table:
create table RandomNumber (number float)
insert into RandomNumber values (rand())
Then take a random number and store the new number in the 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)
The SQL Server integer number ranges between -2147483648 and 2147483647, and the random number floats between 0.0 and 1.0. Thus, it -2147483648 + 4294967295 * number
should cover the entire range of available integers.
The transaction ensures that only one connection at a time reads and stores the new number. Thus, the numbers are random even on different connections to SQL Server. (By the way, I voted on gbn's answer, it seems much simpler.)
source to share
You can use a table with just an id field to create unique nunbers to use as a sample:
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
source to share