BG100 BG100 - 1 year ago 57
SQL Question

Creating Nondeterministic functions in SQL Server using RAND()

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 Source

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.