(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 :).