Conor vs. Runtime Constant Functions

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

 

Happy Querying!

 

Conor

Comments

• Anonymous
  April 25, 2010
  Oh, i was wondering about that. Is the same done for string FUNCTIONs? What about string FUNCTION that take a COLUMN as an argument?

• Anonymous
  May 03, 2010
  The comment has been removed

• Anonymous
  July 25, 2010
  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.