Conor Cunningham’s blog on SQL Server, data-driven applications, and pretty much whatever other random stuff he decides to post.
(more questions resulting from SQL Bits – some of these answers will be a bit more terse than my usual verbose self :))
A customer asked a different question and I need to introduce a concept to you before I can answer that question, so I will rephrase their question and then do another post with the original question:
What happens when you have a non-deterministic function referenced more than once in a query?
SELECT getdate(), getdate() FROM T
SELECT getdate(), * FROM (SELECT … WHERE T.col < getdate())
(whatever form you want)
There are some scalar functions in SQL Server that are commonly used and completely non-deterministic. getdate() is the most obvious of these. The desired customer intent is that the query will be executed and have one value for getdate(). However, if one were to translate this into the query directly, you could get more than one call with slightly different time values.
So, SQL Server has a concept called “runtime constants” where some expressions are pulled out of the tree and executed before rows are fetched. The result is cached and re-used in all places within the query.
getdate() is one – I will leave it as an exercise for the reader to ponder which other built-in scalar functions are in this category as well :).
Oh, i was wondering about that.
Is the same done for string FUNCTIONs? What about string FUNCTION that take a COLUMN as an argument?
great to know getdate() is not executed on each row fetch.
Are there any clues in the execution plan that indicate if the execution of a scalar function is NOT cached in this way?
String functions that are not "constant" are executed per-row.
As for whether this information is exposed or not, I do not believe we document this. However, you might be able to look at the showplan xml and see some details of this. I would not depend on it from release to release, as this is the kind of thing that we might change a bit while writing the new features for each release.