9

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?

BG100
  • 4,481
  • 2
  • 37
  • 64

3 Answers3

19

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 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
Lieven Keersmaekers
  • 57,207
  • 13
  • 112
  • 146
  • 4
    +1 For the view trick. I just remembered about that as well. It isn't because of the determinism though. `getdate()` is allowed in a function. Obviously that function can't be then used where a deterministic function is required such as a persisted computed column. It is because of the side effects. The error raised is "Invalid use of a side-effecting operator 'rand' within a function." – Martin Smith Aug 20 '10 at 13:59
  • Thanks! I'd heard about doing this as a work around for the NEWID() function, but didn't realise it would work for RAND() as well. – BG100 Aug 20 '10 at 14:22
13

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.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • ah, I understand now! I didn't think about the internal storage of the last random number. I can see how this sets it apart from other nondeterministic functions. Thanks! – BG100 Aug 20 '10 at 14:24
  • check out http://sqlfascination.com/tag/randomstring/ It tells you exactly how to do this. – baash05 Apr 12 '12 at 00:25
4

I found this solution that doesn't create a view:

Basically:

Instead of 

SET @R = Rand()

Use

SET @R = ABS(CHECKSUM(PWDENCRYPT(N''))) / 2147483647.0

In my case I wanted a number between 1 and 10:

ROUND(((10 - 1 -1) * ( ABS(CHECKSUM(PWDENCRYPT(N''))) / 2147483647.0) + 1), 0))

ROUND(((@max - @lower -1) * ( ABS(CHECKSUM(PWDENCRYPT(N''))) / 2147483647.0) + @lower), 0))

If you want a full explanation: Using (Or Simulating) Rand() In A T-Sql User-Defined Function

SqlZim
  • 37,248
  • 6
  • 41
  • 59
Sanchitos
  • 8,423
  • 6
  • 52
  • 52