SQL random number not working
declare @fieldForceCounter as int
declare @SaleDate as dateTime
declare @RandomNoSeed as decimal
set @fieldForceCounter = 1
set @SaleDate = '1 Jan 2009'
set @RandomNoSeed = 0.0
WHILE @fieldForceCounter <= 3
BEGIN
while @SaleDate <= '1 Dec 2009'
begin
INSERT INTO MonthlySales(FFCode, SaleDate, SaleValue) VALUES(@fieldForceCounter, @SaleDate, RAND(@RandomNoSeed))
set @saleDate = @saleDate + 1
set @RandomNoSeed = Rand(@RandomNoSeed) + 1
end
set @SaleDate = '1 Jan 2009'
set @fieldForceCounter = @fieldForceCounter + 1
END
GO
This T-SQL command was to insert random values into the column " SaleValue
" in the table " MonthlySales
".
But it inserts '1' every time.
What could be the problem?
source to share
Two problems:
- First, the rand () function returns a number between 0 and 1.
- Second, when rand () is called multiple times in a single request (for example, for multiple rows in an update statement), it usually returns the same number (which I suspect is trying to solve your algorithm by breaking it down into multiple calls)
My favorite way to get around the second problem is to use a function that is guaranteed to return a unique value every time, like newid (), converts it to varbinary and uses it as a seed :)
Edit: after some testing it seems like you need to try using a different datatype for @RandomNoSeed
; float behaves slightly differently than decimal but still fits a fixed value, so I would recommend avoiding using @RandomNoSeed altogether and just using:
INSERT INTO MonthlySales(FFCode, SaleDate, SaleValue)
VALUES(@fieldForceCounter, @SaleDate, RAND(convert(varbinary,newid())))
source to share
You have serious problems here ...
Decimal problems
The default precision / scale for decimal is 38.0. This way you are not keeping the decimal part.
So you only use RAND (0) for the 1st iteration and RAND (1) for all subsequent iterations, which is 0.943597390424144 and 0.713591993212924
I can't remember how rounding / truncation is applied, and I don't know what data type SalesValue is, but rounding will give "1" every time.
Now if you fix that and declare the decimal correctly ...
Seeding problems
RAND accepts an integer. Seeding with 1.0001 or 1.3 or 1.999 gives the same value (0.713591993212924).
So, "Rand (1.713591993212924) + 1" = "RAND (1) + 1" = "1.713591993212924" for each subsequent iteration.
Back to square ...
Fix
- Get rid of @RandomNoSeed
- Either: generate a random integer value with CHECKSUM (NEWID ())
- Or: generate a random floating point value using RAND () * CHECKSUM (NEWID ()) (no longer interested in the seed)
source to share