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.

asked 10 Dec '09, 20:25

Jon%20Greisz's gravatar image

Jon Greisz
571979
accept rate: 0%

edited 11 Dec '09, 10:46

Zote's gravatar image

Zote
1.7k364050


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.

permanent link

answered 11 Dec '09, 03:06

Glenn%20Paulley's gravatar image

Glenn Paulley
10.7k568104
accept rate: 43%

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?

(11 Dec '09, 13:35) Breck Carter

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

(11 Dec '09, 14:52) Jon Greisz

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.

permanent link

answered 11 Dec '09, 15:42

Glenn%20Paulley's gravatar image

Glenn Paulley
10.7k568104
accept rate: 43%

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

(11 Dec '09, 18:45) Jon Greisz
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×28
×18
×4

question asked: 10 Dec '09, 20:25

question was seen: 907 times

last updated: 11 Dec '09, 15:42