Jul 13, 2006

Accessing non-deterministic functions in UDF

For those who didn't know, the following function definition will throw an error in SQL Server 2000.

create function fn1()
returns datetime
as
begin
return getdate()
end

The error thrown is "Invalid use of 'getdate' within a function".

Thats because built in non deterministic functions cannot be used inside UDF. Well there is a work around for this.

Lets create a view like this:

create view curdate
as
select getdate() as dt

And create a function this way.

create function fn1()
returns datetime
as
begin
return (select dt from curdate)
end

Now calling the function will get me the current date.

But, calling getdate() directly in an UDF in SQL Server 2005 doesn't throw any error. Meaning, the function definition given in the beginning of this post will actually work fine in SQL Server 2005.

Now, the question is why couldn't you call built-in non deterministic function inside your UDF in SQL Server 2000 and why was it allowed now?

But then the following function when you try create in SQL Server 2005 throws an error.

create function fn2()
returns float
as
begin
return rand()

end

And the error is "Invalid use of side-effecting or time-dependent operator in 'rand' within a function". The same error is thrown if I try to call newid()

Well, then why was getdate() allowed?

Any idea?

2 comments:

daniel said...

wow, you got some serious answers here :)

anyway, thanks for the workaround

sourabh said...

SQL Server 2005 has allowed some of the non-deterministic functions that were not allowed in 2000. In 2005 you can now use
CURRENT_TIMESTAMP
@@MAX_CONNECTIONS
GET_TRANSMISSION_STATUS
@@PACK_RECEIVED
GETDATE
@@PACK_SENT
GETUTCDATE
@@PACKET_ERRORS
@@CONNECTIONS
@@TIMETICKS
@@CPU_BUSY
@@TOTAL_ERRORS
@@DBTS
@@TOTAL_READ
@@IDLE
@@TOTAL_WRITE and
@@IO_BUSY

You still cannot use
NEWID
RAND
NEWSEQUENTIALID and
TEXTPTR

I got that detail from SQL Server Magazine, and may have changed since publication, but the end result is - it is not a bug, you can use GETDATE() in functions,

Here is more directly from the article

“SQL Server 2005 Books Online (BOL) doesn’t provide a reason for this change or the distinction between the functions that are and aren’t allowed, but you’ll notice that the functions you can’t use have side effects. That is, the SQL Server 2005 non-deterministic functions that aren’t allowed make internal changes in the database or the system and leave a mark (such as a change to an interval value) that they have been invoked. And UDFs aren’t allowed to have side effects. For example, when SQL Server invokes the RAND function it changes an internal value that’s used to calculate the seed for the next invocation of the function. This means that had SQL Server allowed you to invoke RAND within a UDF, the invocation of the UDF would have changed the result of subsequent invocations of RAND outside the UDF as well. For this reason, you can’t use RAND in a UDF, even in SQL Server 2005.”

Post a Comment