Random numbers from the database

Question

EDIT : Duplicate How to return random numbers as a column in SQL Server 2005?

Hi How can I generate random numbers in a mssql 2005 server.

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

UPDATE: I need the fastest method, generate numbers from large tables, datepart, the same magic calculation is really slow with lots of rows

UPDATE thanks for the answers, but o finally used this solution

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

for random numbers from -5 to 5, and the bonus is about the same number of events for each number

Answer 1

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

Answer 2

, ...

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

Answer 3

Google: 1 2...

---

SELECT

, RAND() SQL Server SELECT . , :

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

. SELECT. , SQL Server 2000, UDF.

, :

CREATE VIEW vRandNumber
AS
SELECT RAND() as RandNumber

, UDF rand(), . UDF, , View.

, :

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

, SELECT, 0 1 :

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

RandNumber , , , , :

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

Answer 4

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