How do I generate random number for each row in a TSQL Select?

Question

I need a different random number for each row in my table. The following seemingly obvious code uses the same random value for each row.

SELECT table_name, RAND() magic_number 
FROM information_schema.tables

I'd like to get a INT or a FLOAT out this. The rest of the story is I'm going to use the random number to create an random date offset from a know date, e.g. 1-14 days offset from a start date.

This is for Microsoft SQL Server 2000.

Answer 1

When called multiple times in a single batch, rand() returns the same number.

I'd suggest using convert(varbinary,newid()) as the seed argument:

SELECT table_name, 1.0 + floor(14 * RAND(convert(varbinary, newid()))) magic_number 
FROM information_schema.tables

newid() is guaranteed to return a different value each time it's called, even within the same batch, so using it as a seed will prompt rand() to give a different value each time.

Edited to get a random whole number from 1 to 14.

Answer 2

Take a look at SQL Server - Set based random numbers which has a very detailed explanation

Answer 3

select newid()

or possibly this select binary_checksum(newid())

Answer 4

try using a seed value in the RAND(seedInt). RAND() will only execute once per statement that is why you see the same number each time.

Answer 5

If you don't need it to be an integer, but any random unique identifier, you can use newid()

SELECT table_name, newid() magic_number 
FROM information_schema.tables

Answer 6

You would need to call RAND() for each row. Here is a good example

http://dotnet.org.za/calmyourself/archive/2007/04/13/sql-rand-trap-same-value-per-row.aspx

Answer 7

Do you have an integer value in each row that you could pass as a seed to the RAND function?

To get an integer between 1 and 14 I believe this would work:

FLOOR( RAND(<yourseed>) * 14) + 1

Answer 8

The Rand() function will generate the same random number, if used in a table SELECT query. Same applies if you use a seed to the Rand function. An alternative way to do it, is using this:

SELECT ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) AS [RandomNumber]

Got the information from here, which explains the problem very good.

Answer 9

I toyed with a few solutions, and provided links to others, here:

http://thehobt.blogspot.com/2009/03/check-your-lucky-numbers-random-number.html

Answer 10

Try to use it http://praveenbattula.blogspot.com/2009/05/how-to-generate-random-numbers-in-t-sql.html