non-deterministic functions

one of the functions that i use is found to be non-deterministic function. So what does this mean w.r.t performance?
will this function be recompiled every time it is used?

this function uses data from 2 oltp tables which will have new records very often. so for the same input values if i get 10 records for first run i might get 15 in the second run because of inserts in the underlying tables. i also use date calculations inside the function which will make the function non-deterministic. But without date calculations should this function be termed non-deterministic because of changes in the output for same input values?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave BaldwinFixer of ProblemsCommented:
Yes.  non-deterministic basically means that you won't necessarily get the same answer each time.  That's not an error, just a condition.
JyozealAuthor Commented:
thanks for your reply. i forgot another part of my question. so for non deterministic functions will sql cache the plans or not? will it make any difference in plan caching between using subquery and cte in this function because of its non-deterministic nature?
Dave BaldwinFixer of ProblemsCommented:
I have no idea about that part.  Please click on "Request Attention" to get others to look at your question.  You could also open another question specifically about that.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
A non-deterministic function cannot be used to get an optimized query, the values are seen as varying and unknown. No caching of the corresponding condition can be done. This might cause degraded performance or not.
A CTE usually can be optimized, and hence improve speed.
In fact you can't tell for sure, as it depends on a lot of factors.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>will this function be recompiled every time it is used?
no. non-deterministic has nothing to do with compilation/recompilation

>so for the same input values if i get 10 records for first run i might get 15 in the second run because of inserts in the underlying tables.

this also has nothing to do with "deterministic". as "same input + same output" also refers to the table data.

>But without date calculations should this function be termed non-deterministic because of changes in the output for same input values?
date calculations also have nothing to do with a function being deterministic or not.

let's take some concrete examples:
the getdate() function is non-deterministic, as the returned value will be different (for an obvious reason)
the random() funciton is non-deterministic.

if you have a function that accepts a date value, and does some maths on the parameter, will be (can be) deterministric, because as long as you pass the same value, it will return the same result.

for the list of deterministic functions in sql server, see here:
as you can see, some functions are sometimes deterministic, depending on the arguments

deterministic vs cache of plans:
there is no direct correlation between a function being deterministic (or not) vs the sql plan caching of the query.
if the values are bound or returned by functions, the sql engine cannot know, in advance, which values to check against, and will determine some plan, "hopefully" the best one.
the sql plan does consider the returned value(s) of functions vs the statistical data of the tables and indexes, so having a function in there that returns a value or directly a found value will not change the results

what CAN be cached is that if the function is deterministic, and has no correlated data input from the data record, it's output value will be "cached" for the sql run/execution and not be executed for each row.

for example, if you have this:
declare @p int
set @p = 100
select yourfunction(@p) x , t.* from your_table

the function shall be called once (if it's deterministic)  or once for each row of the table (if it's non-deterministic)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
...2 more cents :
<you can simply make a function deterministic by adding schemabinding option. it shoul make it much faster.>
see more @:

Slow query using non-deterministic user defined function
JyozealAuthor Commented:
Thanks for your comments. I cannot make my function deterministic because i use getdate() .
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you always could add a date paramter to the function that will need to get passed the value of getdate() ...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.