SQL Random number not working

Question

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 supposed to insert random values in the 'SaleValue'-column in the 'MonthlySales'-table.

But it is inserting '1' every time .

What can be the problem?

Answer 1

Just a guess, but often rand functions generate a number from 0-1. Try multiplying your random number by 10.

Answer 2

Two problems:

• Firstly, the rand() function returns a number between 0 and 1.
• Secondly, when rand() is called multiple times in the same query (e.g. for multiple rows in an update statement), it usually returns the same number (which I suspect your algorithm above is trying to solve, by splitting it into multiple calls)

My favourite way around the second problem is to use a function that's guaranteed to return a unique value each time, like newid(), convert it to varbinary, and use it as the seed :)

Edit: after some testing, it seems you'll need to try using a different datatype for @RandomNoSeed; float behaves somewhat different to decimal, but still approaches a fixed value, so I'd recommend avoiding the use of @RandomNoSeed altogether, and simply use:

INSERT INTO MonthlySales(FFCode, SaleDate, SaleValue) 
VALUES(@fieldForceCounter, @SaleDate, RAND(convert(varbinary,newid())))

Answer 3

You have major issues here...

Decimal issues

The default precision/scale for decimal is 38,0. So you aren't storing any decimal part.

So you are only using RAND(0) for 1st iteration and RAND(1) for all subsequent iterations, which is 0.943597390424144 and 0.713591993212924

I can't recall how rounding/truncation applies, and I don't know what datatype SalesValue is, but rounding would give "1" every time.

Now, if you fix this and declare decimal correctly...

Seeding issues

RAND takes an integer seed. 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 every subsequent iteration.

Back to square one...

To fix

• Get rid of @RandomNoSeed
• Either: Generate a random integer value using CHECKSUM(NEWID())
• Or: generate a random float value using RAND() * CHECKSUM(NEWID()) (Don't care about seed now)

Answer 4

Here is an example http://praveenbattula.blogspot.com/2009/05/how-to-generate-random-numbers-in-t-sql.html