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, *
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
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()
RETURN (SELECT RandNumber FROM vRandNumber)
Finally, you can use this function in any SELECT to now return a random number between 0 and 1 per row:
SELECT dbo.RandNumber(), *
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)
RETURN @Min + (select RandNumber from RetRandNumber) * (@Max-@Min)
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.