When GETDATE() is not a constant : SQL Feeds

When GETDATE() is not a constant

by Andrew Kelly via Andrew Kelly on 2/27/2008 3:53:00 PM

A short while ago I was collecting wait stat information at a client and ran across a very peculiar situation that I would like to share. Let me start by saying that for years I have coded with the understanding that when you include a system function in the SELECT list of a TSQL statement the function was evaluated once at the beginning and that same value was used for each row returned. I am talking about a statement such as this:

SELECT GETDATE(), CompanyName FROM Customers

The output expected looks like this:

2008-02-27 10:22:34.270    Alfreds Futterkiste
2008-02-27 10:22:34.270    Ana Trujillo Emparedados y helados
2008-02-27 10:22:34.270    Antonio Moreno Taquería
2008-02-27 10:22:34.270    Around the Horn
2008-02-27 10:22:34.270    Berglunds snabbköp
2008-02-27 10:22:34.270    Blauer See Delikatessen
2008-02-27 10:22:34.270    Blondesddsl père et fils
2008-02-27 10:22:34.270    Bólido Comidas preparadas

...

Please note that I am not talking about a User Defined Function or once that takes a column as an input to determine the result. In this case I am specifically referring to GETDATE(). As you can see all the datetime values are exactly the same as expected.

But what I experienced the other day was not as expected and quite concerning. What I got was for a single SELECT I received several different values for the GETDATE() column in the result set. This did not happen every time but happened enough times over a few days that I certainly took note of it. Now let me give a little more background because it was not just a SELECT. It was actually an INSERT INTO with the SELECT from a DMV. Not that any of this should matter anyway but for consistency sake let me give you the actual code (with a slight enhancement for demo purposes). I added an extra column called R_ID that is used to store the unique value of each loop and I placed the Insert in a WHILE loop so it can be exercised. In real life the Insert was only executed several times each day. The code below can be used to see if your system is experiencing this behavior or not. Depending on the version and service pack you may have a different number of Waits but in my case with Microsoft SQL Server 2005 - 9.00.3159.00 (Intel X86) I get 201 rows for each pass. I suspect the version has everything to do with this behavior. The system at the time was running an older version of SQL Server 2005 which was: 9.00.2047.00. If anyone finds that their server returns different values of GETDATE() for any iteration of the select I would really be interested in what version of SQL Server you are running. There is a LOT of code out there that relies on the value acting like a constant and having the same value in each row of a single SELECT statement. I suspect this is a bug in that particular version but who knows...

Please note that the sole purpose of the WHILE loop is just to give you a better chance of seeing the issue if it appears. We are looking for a difference in the datetime values for each instance of the SELECT only and not from loop to loop.

SET NOCOUNT ON

IF OBJECT_ID(N'[dbo].[wait_stats]',N'U') IS NULL
    CREATE TABLE [dbo].[wait_stats]
        ([R_ID] INT not null,
        [wait_type] nvarchar(60) not null,
        [waiting_tasks_count] bigint not null,
        [wait_time_ms] bigint not null,
        [max_wait_time_ms] bigint not null,
        [signal_wait_time_ms] bigint not null,
        [capture_time] datetime not null default getdate())

DECLARE @x INT
SET @x = 1

WHILE @x < 100
BEGIN

    INSERT INTO [dbo].[wait_stats] ([R_ID], [wait_type], [waiting_tasks_count], [wait_time_ms], [max_wait_time_ms], [signal_wait_time_ms], [capture_time])
        SELECT @x, [wait_type], [waiting_tasks_count], [wait_time_ms], [max_wait_time_ms], [signal_wait_time_ms], GETDATE()
            FROM sys.dm_os_wait_stats

SET @x = @x + 1
END

-- Find the ones that have odd counts. If this returns any rows you had a difference in time for a single itteration.

SELECT [R_ID], COUNT(*) AS [Totals], [capture_time]
FROM [dbo].[wait_stats]
GROUP BY [R_ID], [capture_time] HAVING COUNT(*) <> 201

**Updates**

I have some new and very important information about this subject and chose to put it in a new blog post that can be found here:

http://sqlblog.com/blogs/andrew_kelly/archive/2008/03/01/when-a-function-is-indeed-a-constant.aspx