Do functions really default to DETERMINISTIC?

[Former Member]

From the online books:

User-defined functions are treated as deterministic unless they are specified as NOT DETERMINISTIC when created.

We have some functions that look things up in the database, and seem to work correctly. So I would be really surprised if this is true.

[Former Member · Accepted solution]

Yes, Jon, they do. Unless the function is declared as NOT DETERMINISTIC, the server is free to cache prior executions and re-use the results if the function is called again with the same parameters.

[Breck_Carter · Accepted solution]

The phrase "free to cache" does not mean the server will re-use the results, just that it can, correct? So if you are expecting NOT DETERMINISTIC behavior, but the function is declared or defaulted to DETERMINISTIC, you may or may not get function caching / result re-use... correct?

[Former Member · Accepted solution]

Interesting, I'll need to have someone check our functions and add the not deterministic. Thanks, Jon

[Former Member]

As Breck indicated, "free to cache" means that the server may (or may not) re-execute the UDF when another invocation of the function is made with the same parameters. The server maintains an LRU cache of function parameters and the function's result, and will perform a look-aside into the cache before it goes to the effort of creating a new procedure context and re-executing the procedure.

If you label a UDF as NOT DETERMINISTIC, then we offer the guarantee that if the function is part of the query's SELECT list, the function will be executed at least once for every row produced by the query.

[Former Member]

Glenn, does it work the same way when called from within a stored procedure?