Some questions are so theoretical that I believe they really do not add too much value if users know that question or not. Here is one such question I am very confident that you agree with my point of view.
Questions:Â What is the Difference Between Deterministic Functions and Nondeterministic Functions?
Answer:Â
Deterministic functions always return the same output result all the time it is executed for same input values. i.e. ABS, DATEDIFF, ISNULL etc.
Nondeterministic functions may return different results each time they are executed. i.e. NEWID, RAND, @@CPU_BUSY etc. Functions that call extended stored procedures are nondeterministic. User-defined functions that create side effects on the database are not recommended.
Now you have read the answer – I have a question back to you.
Did you the difference between deterministic and nondeterministic function before this blog? If no, has it ever impacted your performance in your daily job?
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
6 Comments. Leave new
ANSI/ISO Standard SQL/PSM has a [NOT] DETERMINISTIC option in its procedure declarations. This tells the compiler if the procedure or function is going to be deterministic and that the optimizer can handle it differently. In particular, f(constant) can be computed once then replaced with the results.
Thanks for your comment, Sir!
Thank you Pinal :)
Your welcome Sathish.
Thanks a lot Pinal for this post. Never wondered that Functions can be differentiated in this way too :)
Thanks a lot Ravi Sankar Rao .D