As it is implemented, the RAND() function in SQL Server doesn't let you return a different random number per row in your SELECT statement. For example, if you execute this:

SELECT Rand() as RandomNumber, *
FROM Northwind..Customers

You will see the same number over and over.  However, sometimes, you may want to return a randomly generated number per line in your SELECT.   Here's one way to do that, in SQL Server 2000, using a UDF.

First, we need to create a View that returns a single random number:

CREATE VIEW vRandNumber
AS
SELECT RAND() as RandNumber

The view is necessary because normally in a UDF we cannot use the rand() function, because that would make the function non-determistic.  We can trick the UDF to accepting a random number by using a View.

Once that is set up, we can then create our function:

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

Finally, you can use this function in any SELECT to now return a random number between 0 and 1 per row:

SELECT dbo.RandNumber(), *
FROM Northwind..Customers

You can get even fancier in your RandNumber function by accepting a seed if you like, or allowing for range parameters like this:

CREATE FUNCTION RandNumber2(@Min int, @Max int)
RETURNS float
AS
 BEGIN
 RETURN @Min + (select RandNumber from RetRandNumber) * (@Max-@Min)
 END

Do whatever you need to do, depending on your needs.  If you have existing code that needs to generate random values per row in a table, you may have been forced to write it using cursors or an outside language like VB.  Hopefully, you will find this useful and may be able to re-write some of that using set-based operations in pure T-SQL.

I was surprised, to be honest, that SQL Server executed the function over and over for each line in the SELECT; a UDF is supposed to be deterministic by definition, and since no parameters are being passed on each call, in theory SQL Server should only need to execute the UDF once per SELECT.  However, SQL Server calls UDF over and over for each row, and that allows us to implement our custom RAND() function successfully.

Enjoy!

see also:

• Some SELECTs will never return 0 rows -- regardless of the criteria
• By The Way ... DISTINCT is not a function ...
• A handy but little-known SQL function: NULLIF()
• Is it a String Literal or an Alias?
• Passing an Array or Table Parameter to a Stored Procedure
• SELECT * FROM TABLE -- except for these columns
• In SQL, it's a Case Expression, *not* a Case Statement
• Returning Random Numbers in a SELECT statement