Do functions really default to DETERMINISTIC?

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.

stored-function deterministic cache

asked 10 Dec '09, 20:25

Jon Greisz
571●9●7●9
accept rate: 0%

edited 11 Dec '09, 10:46

Zote
1.7k●36●40●51

flat view

2 Answers:

active answers oldest answers newest answers popular answers

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 Paulley
10.8k●5●76●106
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

flat view

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 Paulley
10.8k●5●76●106
accept rate: 43%

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

(11 Dec '09, 18:45) Jon Greisz

flat view

Your answer

toggle preview

community wiki:

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

learn more about Markdown

Question tags:

stored-function ×28
cache ×26
deterministic ×4

question asked: 10 Dec '09, 20:25

question was seen: 2,706 times

last updated: 11 Dec '09, 15:42

SAP SQL Anywhere

Do functions really default to DETERMINISTIC?

Follow this question

Related questions