Multiple random values in SQL Server 2005

Question

I need to generate multiple random values under SQL Server 2005 and somehow this simply wont work

with Random(Value) as
(
    select rand() Value
     union all
    select rand() from Random

)select top 10 * from Random

Whats the preffered workaround?

Answer 1

I'm currently using this:

with Random(Value) as
(
    select rand(checksum(newid())) Value
     union all
    select rand(checksum(newid())) from Random 
)select top 10 * from Random

but that seems overly hackish :S Why doesnt rand get reevaluated in the first version?

Answer 2

have you tries something like this (found at http://weblogs.sqlteam.com ) :

CREATE VIEW vRandNumber
AS
SELECT RAND() as RandNumber
GO

create a function

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

then you can call it in your selects as normal Select dbo.RandNumber() , * from myTable

or from their comments:

select RAND(CAST(NEWID() AS BINARY(6))), * from myTable