Function determinism

Just recently, while working on another post on multicolumn IN statement I came across another random SQL Server stupidity. Maybe it's an exaggregation but very irritating thing nevertheless. Every other day I find SQL Server's User Defined Functions to be more and more crippled, useless... I have tons of things to write about (and will do) but since I just stumbled upon this one I'll do it now.

Well... seems like UDFs in SQL Server must be deterministic by design. The server disallows you to create any functions that would encorporate anything that Server knows is non deterministic and, first and foremost, that includes system functions like rand() and newid().

create or alter function NonDeterministic () 
returns float
as
begin
 return rand()
end
go

Msg 443, Level 16, State 1, Procedure NonDeterministic, Line 6 [Batch Start Line 121]
Invalid use of a side-effecting operator 'rand' within a function.


Why is that even possible? What is the reasoning behind it? Why a function has to be deterministic in the SQL Server? Well... since people have already found workarounds and it's easy to write function that will pass compilation and will behave undeterministically.. what's the point of all of this?

create or alter view RandomView as
 select rand() as RandomValue
go

create or alter function NonDeterministic () 
returns float
as
begin
 declare @rv float
 select @rv = RandomValue from RandomView
 return @rv
end
go

select dbo.NonDeterministic() as r1, dbo.NonDeterministic() as r2, dbo.NonDeterministic() as r3
from (select 1 as id union all select 2 as id union all select 3 as id) severalrowsofdata;

r1                     r2                     r3
---------------------- ---------------------- ----------------------
0,911081997364809      0,778907737504499      0,824760706292227
0,358165531868303      0,610646082434233      0,353739725288632
0,678415101351906      0,679777567642382      0,893867037873934

(3 rows affected)

There's no such problems in Oracle:

create or replace function NonDeterministic 
return number is
begin
    return dbms_random.value(0,1);
end;
/

select NonDeterministic as r1, NonDeterministic as r2, NonDeterministic as r3
from (
    select 1 as id from dual union all 
    select 2 as id from dual union all 
    select 3 as id from dual
) severalrowsofdata;

        R1         R2         R3
---------- ---------- ----------
,235553683 ,684116267 ,294452895
,486510785 ,207421073 ,845284566
,488748819 ,387015281 ,316854148

Even more. In Oracle ALL functions are non-deterministic by definition! However, you can set them to be deterministic but this still won't disallow anything from the function's body! It's only hinting the database engine that this function will return same output when given same parameters thus allowing for caching its results. You can obviously put yourself in trouble when not paying attention ;) Just take a look:

create or replace function NonDeterministic2
return number deterministic is
begin
    return dbms_random.value(0,1);
end;
/

select NonDeterministic2 as r1, NonDeterministic2 as r2, NonDeterministic2 as r3
from (
    select 1 as id from dual union all 
    select 2 as id from dual union all 
    select 3 as id from dual
) severalrowsofdata;

        R1         R2         R3
---------- ---------- ----------
,0430362939 ,0106835894 ,717701516
,0430362939 ,0106835894 ,717701516
,0430362939 ,0106835894 ,717701516

Not so random anymore, huh?

Hope it helps. Wish hear to why Microsoft designed it this way.

Best