<< Mock objects - testing your classes without test data! | Home | Microsoft StyleCop - Source code analysis for formatting and style >>
Generating random numbers within SELECT queries in SQL Server 2005

I was generating some test data and tried to use the RAND() function to query data. When trying this out on the Northwind database, it returned with the following result:

SELECT LastName, RAND() from Employees
 
LastName (No column name)
Buchanan 0.747783
Callahan 0.747783
Davolio 0.747783
Dodsworth 0.747783
Fuller 0.747783
King 0.747783
Leverling 0.747783
Peacock 0.747783
Suyama 0.747783

The problem is that the randomly generated number is the same for all rows. What I need is a function that will generate the random number differently each time this is called.

After digging around, the easiest way I have found is to

  1. Create a view that generates a random number
  2. Create a function that calls the view

Here are the 2 statements I used:

CREATE VIEW RandomNumberView AS BEGIN     SELECT RAND() as RandNum
GO
 
CREATE FUNCTION GetRandomNumber() 
RETURNS FLOAT 
AS  BEGIN     RETURN SELECT RandNum FROM RandomNumberView
END

Then all you do is call your function (with schema name) to return a new random number:

SELECT LastName, dbo.GetRandomNumber() from Employees
 
LastName (No column name)
Buchanan 0.750829897796789
Callahan 0.669015405308125
Davolio 0.119630891365149
Dodsworth 0.488104954627394
Fuller 0.30583533779782
King 0.359980386714745
Leverling 0.290999223959155
Peacock 0.972126393998601
Suyama 0.388598769319124

Bookmark with :
Digg It! DZone StumbleUpon Technorati Reddit Del.icio.us Newsvine Furl Blinklist
posted @ Wednesday, July 23, 2008 10:06 AM | in Testing

Comments

No comments posted yet.

Post Comment

Title *
Name *
Email
Url
Comment *  


Please add 8 and 6 and type the answer here: