How do I return random numbers as a column in SQL Server 2005?

Question

I'm running a SQL query on SQL Server 2005, and in addition to 2 columns being queried from the database, I'd also like to return 1 column of random numbers along with them. I tried this:

select column1, column2, floor(rand() * 10000) as column3 from table1

...which kinda works, but the problem is that this query returns the same random number on every row. It's a different number each time you run the query, but it doesn't vary from row to row. How can I do this and get a new random number for each row?

Answer 1

You need to use a UDF

first:

CREATE VIEW vRandNumber
AS
SELECT RAND() as RandNumber

second:

CREATE FUNCTION RandNumber()
RETURNS float
AS
  BEGIN
  RETURN (SELECT RandNumber FROM vRandNumber)
  END

test:

SELECT dbo.RandNumber(), *
FROM <table>

Above borrowed from Jeff's SQL Server Blog

Answer 2

For SQLServer, there are a couple of options.
1. A while loop to update an empty column with one random number at a time
2. A .net Assembly that contains a function that returns a random number

Answer 3

select column1, column2, cast(new_id() as varchar(10)) as column3 from table1

Answer 4

Adam's answer works really well, so I marked it as accepted. While I was waiting for an answer though, I also found this blog entry with a few other (slightly less random) methods. Kaboing's method was among them.

http://blog.sqlauthority.com/2007/04/29/sql-server-random-number-generator-script-sql-query/

Answer 5

You might like to consider generating a UUID instead of a random number using the newid function. These are guaranteed to be unique each time generated whereas there is a significant chance that some duplication will occur with a straightforward random number (and depending on what you're using it for could give you a phenominally hard to debug error at a later point)

Answer 6

newid() i believe is very resource intensive. i recall trying that method on a table of a few million records and the performance wasn't nearly as good as rand().

Answer 7

I realize this is an older post... but you don't need a view.

select column1, column2, 
  ABS(CAST(CAST(NEWID() AS VARBINARY) AS int)) % 10000 as column3 
from table1

Answer 8

According to my testing, the answer above doesn't generate a value of 10000 ever. This probably isn't much of a problem when you are generating a random between 1 and 10000, but the same algorithm between 1 and 5 would be noticable. Add 1 to your mod.

Answer 9

This snippet seems to provide a reasonable substitute for rand() in that it returns a float between 0.0 and 1.0. It uses only the last 3 bytes provided by newid() so total randomness may be slightly different than the conversion to VARBINARY then INT then modding from the recommended answer. Have not had a chance to test relative performance but seems fast enough (and random enough) for my purposes.

SELECT CAST(SubString(CONVERT(binary(16), newid()), 14, 3) AS INT) / 16777216.0 AS R