2

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?

Torbjörn Gyllebring
  • 17,928
  • 2
  • 29
  • 22
  • I like questions like this that force me to go to learn something new! Thanks! Hope the answer helped. – Mauro Oct 08 '08 at 15:01

2 Answers2

3

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
Mauro
  • 4,531
  • 3
  • 30
  • 56
  • 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. – Torbjörn Gyllebring Oct 08 '08 at 15:03
0

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?

Torbjörn Gyllebring
  • 17,928
  • 2
  • 29
  • 22