Random numbers from database

Question

EDIT: Duplicate of http://stackoverflow.com/questions/94906/how-do-i-return-random-numbers-as-a-column-in-sql-server-2005

Hello How can I generate random numbers in mssql server 2005.

for example: when I select 500rows from table, each row must have one generated random number, numbers must be generated at runtime

UPDATE: I need the fastest method, generate numbers from large tables, datepart, same magic computations is realatively slow with big amount of rows

UPDATE thanks for answers, but o finally used this solution

SELECT ABS(CHECKSUM(NewId())) % 10 - 5 AS Random

for random numbers from -5 to 5 and bonus is approximately the same number of occurances for each number

Answer 1

easy google: 1 2 ...

---

Returning Random Numbers in a SELECT statement

As it is implemented, the RAND() function in SQL Server doesn't let you return a different random number per row in your SELECT statement. For example, if you execute this:

SELECT Rand() as RandomNumber, *
FROM Northwind..Customers

You will see the same number over and over. However, sometimes, you may want to return a randomly generated number per line in your SELECT. Here's one way to do that, in SQL Server 2000, using a UDF.

First, we need to create a View that returns a single random number:

CREATE VIEW vRandNumber
AS
SELECT RAND() as RandNumber

The view is necessary because normally in a UDF we cannot use the rand() function, because that would make the function non-determistic. We can trick the UDF to accepting a random number by using a View.

Once that is set up, we can then create our function:

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

Finally, you can use this function in any SELECT to now return a random number between 0 and 1 per row:

SELECT dbo.RandNumber(), *
FROM Northwind..Customers

You can get even fancier in your RandNumber function by accepting a seed if you like, or allowing for range parameters like this:

CREATE FUNCTION RandNumber2(@Min int, @Max int)
RETURNS float
AS
 BEGIN
 RETURN @Min + (select RandNumber from RetRandNumber) * (@Max-@Min)
 END

Answer 2

SELECT TOP 500
    CONVERT(INT, CONVERT(VARBINARY(16), NEWID()))
FROM
    dbo.MyTable

Answer 3

I love this trick, its awesome ...

-- 500 random numbers from 0 to 499 
select top 500 ABS(CAST(CAST(NEWID() AS VARBINARY) AS int)) % 500 from sysobjects

Answer 4

It might be helpful to know why you want the random number. For example if you just want to sort the results randomly you can do ORDER BY NEWID() and not worry about generating the random number at all. If you really need the random number later, use @Tim quoted solution.