THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Andrew Kelly

When a Function is indeed a Constant

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.

Published Saturday, March 01, 2008 12:19 PM by Andrew Kelly

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Andrew Kelly : When GETDATE() is not a constant said:

March 1, 2008 11:25 AM
 

EdVassie said:

I found that Getdate() returned multiple values in a INSERT .. SELECT statement for a number of fix levels in SQL Server 2000.  As I needed a consistant time on all rows that were inserted, I just did SET @var = Getdate() before the INSERT, and used @var to provide the required time in the main query.

March 3, 2008 4:39 AM
 

jerryhung said:

I never thought about this issue until your last blog post

Luckily, my recent insert of 300,000+ records all share the exact same getdate() value, EVERY TIME (RevisionDate = getdate() inside a transaction)

However, I have changed my insert code to use a variable instead, to make sure I don't get surprises.

March 3, 2008 3:28 PM
 

Seraph2047@hotmail.com said:

How To Use MYSQL's "limit" function in Microsoft Sql server?

for expmple:

a database have 1,000,000 record,

I want to read record at order 1000 - 1100.

in MySQL:  

"Select * from Table1 limit 1000,100" ;

I serach some blog and technic webpage how to read at mssql,

in MsSQL:

"Select * from (Select top 1100 * form Table1 order by DESC) as T1 order by ASC"

Bnt this expression is too slowly!!! Question: how to optimize? thanks.

March 24, 2008 3:35 AM
 

JRStern said:

Yup.  Just ran into the non-consistent getdate() today my own self, on a (now) retro version of SQL 2005 32-bit:

Microsoft SQL Server 2005 - 9.00.3282.00 (Intel X86)

Aug  5 2008 01:01:05

Copyright (c) 1988-2005 Microsoft Corporation

Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

Thanks for confirminng I'm not crazy - or at least keeping me company!

April 28, 2010 9:31 PM
 

Paul White: Page Free Space said:

The humble Compute Scalar is one of the least well-understood of the execution plan operators, and usually

September 4, 2012 6:22 PM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement