Multiple random values in SQL Server 2005

Question

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

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

What's the preferred workaround?

Answer 1

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