Creating Nondeterministic functions in SQL Server using RAND()

Question

After a bit of searching and reading the documentation, it's clear that you can write user defined functions in SQL Server that are marked as either deterministic or nondeterministic depending on which built-infunctions are used within the body.

RAND() is listed under the nondeterministic functions (see msdn article). So why can't I use it in a function?

Answer 1

Because it has side effects.

Constructs with side effects are not allowed in a function. The side effect that it has is to change some internal state that keeps track of the last rand() value issued.

I think you can get around it by including it in a View definition then selecting from the View.

Answer 2

Using a View might work for you.
From Returning Random Numbers from a select statement

CREATE VIEW vRandNumber
AS
SELECT RAND() as RandNumber

The view is necessary because as you already found out, a UDF you cannot use the rand() function, because that would make the function non-determistic. You can trick the UDF to accept a random number by using a View.

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