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. The question is about deterministic functions and nondeterministic functions.
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?
Where Nondeterministic Functions Actually Matter
The difference feels theoretical until you try to index something. SQL Server lets you index a computed column, or create an indexed view, only when the expression is deterministic. A computed column that uses GETDATE, for example, cannot be persisted or indexed, because its value would change without the row changing.
Your own functions follow the same rule. A scalar function counts as deterministic only when it is created WITH SCHEMABINDING and uses only deterministic functions inside. You can check what SQL Server thinks with SELECT OBJECTPROPERTY(OBJECT_ID('dbo.MyFunction'), 'IsDeterministic'), which returns 1 or 0.
One more fun detail for interviews: RAND() without a seed is evaluated once per query, so every row gets the same number, while NEWID() gives a new value on every row. That is why ORDER BY NEWID() is the common trick to return rows in a random order.
There is a practical side too. If a report must show the same numbers when you run it again tomorrow, save the value of GETDATE into a variable or a column once at the start, instead of calling it again in every step. That way every part of the report uses the same moment in time.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.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