getdate() in UDF

Dinesh.T.K.

unread,

Jun 28, 2001, 5:39:13 PM6/28/01

to

CRS,

getdate() is a nondeterministic function which means it doesn't
return the same value each time it is referenced with the same input
params.
Functions like this are *not* allowed in UDFs (an other example is RAND())

What you could do is send GETDATE() as a param.

Dinesh.

Byron Sisson

unread,

Jun 28, 2001, 5:42:45 PM6/28/01

to

From Books online:

'Built-in functions that can return different data on each call are not
allowed in user-defined functions.'

So the answer is "because they said so".

"CRS" <c...@hotmail.com> wrote in message
news:107f01c10017$cc6f5260$9be62ecf@tkmsftngxa03...
> Hi all,
>
> Who would like to explain why getdate() is not allowed in
> SQL 2000 UDF?
>
> thousands of thanks

CRS

unread,

Jun 28, 2001, 5:51:46 PM6/28/01

to

Thanks. Very clear explanation. But why doesn't UDF allow
nondeterministic functions? Any in-depth to share?

>.
>

CRS

unread,

Jun 28, 2001, 6:04:42 PM6/28/01

to

Why do they say that? :)

>.
>

BP Margolin

unread,

Jun 28, 2001, 9:33:42 PM6/28/01

to

CRS,

> Any in-depth to share?

Remember you asked for this :-)

SQL is based on sets ... operations on the sets, in theory, occur
simultaneously on all the members of the set ... in other words, operations
do not occur by "walking" through the set one member at a time.

To try to make that clearer ... if you code

use pubs
select getdate( ) from authors

You will get back the exact same value for getdate( ) for each row of
authors because the getdate( ) is row independent and thus is applied to the
table (the set) and not to each row (the members) of the table.

Likewise, when you call a scalar UDF, SQL (the language) is supposed to act
as if the UDF is being applied simultaneously to each row in the table.
Obviously in reality this is not how things really work, but for SQL to be
consistent with its mathematical foundations this is how it is supposed to
look. The problem with the getdate( ) function in particular is that because
in reality a scalar UDF is applied row-by-row, if the UDF calls the
getdate( ) function then the UDF may very well produce different results
than the theory demands.

Another way to look at it ... as before, a UDF is supposed to be applied to
each row of a table simultaneously. Thus if you have a table with a column
c1, and every row of the table has the value 6, then you would expect, and
SQL (the language) demands, that the scalar UDF return the same value for
every row of the table. Simply put, given the same input, you expect to get
the same output. Reasonable enough, right? But if the scalar UDF calls the
getdate( ) function, then there is a chance that your calculations within
the UDF will depend upon the value returned by the getdate( ) function, and
thus, because in reality, a scalar UDF in SQL Server is currently applied
row-by-row, the return value from the UDF may differ row-by-row even though
the input was the same.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

"CRS" <c...@hotmail.com> wrote in message

news:120001c1001c$86d1b860$9ae62ecf@tkmsftngxa02...

CRS

unread,

Jun 28, 2001, 10:49:17 PM6/28/01

to

Yes,yes, that's exactly what I want to know.

Thank you for your time and generosity!

CRS

>.
>