Saturday, March 01, 2008

When a Function is indeed a Constant

by Andrew Kelly via Andrew Kelly on 3/1/2008 4:19:19 PM

In my last blog post:

 http://sqlblog.com/blogs/andrew_kelly/archive/2008/02/27/when-getdate-is-not-a-constant.aspx

I mentioned that I ran across a situation in which GETDATE() used in a SELECT statement occasionally returned more than 1 value for the result set. That sparked quite a debate amongst SQL Server programmers who belonged to one of two camps in how they believed this really should behave.  For instance if you run the following SQL statement:

           SELECT GETDATE() AS [The Datetime] FROM sys.sysobjects

Should you expect all the rows to have the same DateTime value or would you expect a different value for each row? Now in this simple example it would be easy to say that the query was so fast that there was not enough elapsed time between rows to give a different DateTime value and you could very well be correct. But this next example calls a UDF that introduces some lag as a result of the WHILE loop and returns GETDATE(). If you run that example you will definately see differences in the return from the function. But what about the plain GETDATE()?  Will it be the same value for all rows or similar to the UDF output? After all they are both spitting out GETDATE() right?  Well try it and see.

USE tempdb
go
CREATE FUNCTION dbo.test$wait()

RETURNS DATETIME
AS
   BEGIN
       DECLARE @I INT SET @I = 1
       WHILE @I < 10000
         BEGIN
           SET @I = @I + 1
         END
       RETURN (GETDATE())
   END
GO
SELECT GETDATE() AS [Getdate],dbo.test$wait() AS [UDF]
    FROM master.sys.sysobjects

As I am sure most of you will see it turns out that GETDATE() or any other non-deterministic runtime constant scalar function will indeed act as a constant for the life of the query execution. What does that mean exactly?  Well in a nutshell it means these functions will be evaluated once at the beginning of the query execution and that value will be used for all resulting rows.  You will always get the same value and if you don't you have a bug. This behavior was confirmed today by one of the SQL Server developers who maintains this code and guarantees this behavior.

While this may not be news to many of you (I was always under the impression this was the intended behavior) but it is to many others. There has been a lot of code written over the years that expects functions like this to return a potentially different value for each row in a single Select statement. That assumption is wrong and I wanted to get the word out there in order to minimize any future coding errors in this regard. As you can see in my original blog post that there is at least 1 older revision of SQL Server in which this was not the case, but don't let that fool you into thinking that is the intended behavior. Keep this behavior in mind as you code along in the future.