Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

A question based on do-functions-really-default-to-deterministic:

When creating functions without the [NOT] DETERMINISTIC clause, the server is free to treat them as deterministic.

This is documented both for ASA 8 and for newer versions up to SA 12.

I'm about to add that particular clause for all functions (and a lot of them do have side-effects), but beforehand I'd like to know if the server's treatment of functions has (significantly) changed between ASA 8 and SA 12 w.r.t. deterministic behaviour - i.e. will SA 12 make more use of function result caching than ASA 8?

Note that I'm generally using those functions with side-effects in simple queries such as

select fnMyFunction('blah');

so there's no need to call the same function multiple times within the same query. (For queries that use functions inside select lists, where clauses and the like, I would expect there have been optimizations to cache results.)

asked 16 Jul '10, 15:15

Volker%20Barth's gravatar image

Volker Barth
accept rate: 34%

Sidenote: I have always had the impression the NON DETERMINISTIC clause had been introduced in V9...but it was in 8.0.1, and so I could have used in all those years in my V8 setup. Some kind of deja vĂ», methinks:)

(16 Jul '10, 15:18) Volker Barth

The documentation is correct that UDFs whose determinism is unspecified are treated as if DETERMINISTIC was actually declared.

It is difficult to answer whether or not SQL Anywhere 11/12 makes "more" use of cached results than was the case with Version 8. Even with Version 8, one could offer no guarantees to the number of times a function was evaluated because changes in context (ie access plan changes from invocation to invocation) could change the pattern of evaluation, and the sequence of parameter values used to invoke the function. All of that is equally true with Version 11/12, and even more so because predicate optimization in Version 11/12 is completely different than what existed in Version 8.

permanent link

answered 16 Jul '10, 21:42

Glenn%20Paulley's gravatar image

Glenn Paulley
accept rate: 43%

So I conclude that if I want to have guaranteed not-deterministic behaviour, I should specify the appropriate clause. I'm gonna do that - for both kinds of functions:)

(17 Jul '10, 10:25) Volker Barth

Excellent idea, if for no other reason than self-documentation.

(17 Jul '10, 11:56) Glenn Paulley
Your answer
toggle preview

Follow this question

By Email:

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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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:


question asked: 16 Jul '10, 15:15

question was seen: 2,630 times

last updated: 16 Jul '10, 21:42