Multiple random values in SQL Server 2005

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?

From stackoverflow

• 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?

  Peter : That will have as much entropy as newid(). checksum() and rand() are both deterministic in this context.

• 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
  

  Torbjörn Gyllebring : Thanks! Seems like the solution from their comments is about the samething that I came up with after some tinkering. Really odd that it doesnt get revaluated.