Should Getdate() in WITH clause be declared as variable?

Question

I understand that in general it is useful to declare the current date as a variable:

DECLARE @CurrentDate as DateTime
SET @CurrentDate = Getdate()

My understanding is that this samples the current date/time once and records it as a static variable. This is useful to ensure that the "current time" is invariant for the duration of the query and also avoids repeatedly using Getdate() which is more costly than fetching the value of a variable.

My question is.... is this required in the case of a recursive WITH clause which appears to fetch the date on a single occasion?

For example, consider the following code:

WITH CalendarSequence as(
  SELECT Getdate() as RollingDate
  UNION ALL
  SELECT DateAdd(month, -1, RollingDate) as RollingDate
  FROM CalendarSequence
  WHERE DateAdd(month, -1, RollingDate) > Convert(date, '2016-01-01')
)
SELECT 
Year(CalendarSequence.RollingDate)*100+Month(CalendarSequence.RollingDate) as MissingYearMonth
FROM CalendarSequence
LEFT OUTER JOIN TableName
ON Year(CalendarSequence.RollingDate)*100+Month(CalendarSequence.RollingDate) = TableName.YearMonthField
WHERE TableName.YearMonthField is NULL

This query generates a temporary table of dates and compares it against an arbitrary table to highlight individual months with no activity/data.

For the purposes of this specific piece of code it doesn't matter too much if Getdate() is being used multiple times (unless the month changes during query execution!) but this is not universally true for similar queries. Part of my motivation for asking the question is to get a better sense of what the WITH function is doing and if it has any surprising behavior.

Answer 1

Executive Summary: If you want a single value for a date/time, capture it in a variable and use it as needed. It makes your intent clear and avoids issues that may arise with software updates and other subtleties.

The following code, tested with SQL Server 2008 R2, demonstrates that not all select statements are equal. While GetDate() appears to be a runtime constant for each instance (and possibly all instances), the issue is more subtle than one might think.

GetDate() may be constant across all columns and rows.

-- Constant across all columns and rows.
with Murphy as (
  select GetDate() as A, GetDate() as B, 1 as Rows
  union all
  select GetDate(), GetDate(), Rows + 1
    from Murphy
    where A = B and Rows < 1000000 )
  select Min( A ) as MinA, Max( A ) as MaxA, Min( B ) as MinB, Max( B ) as MaxB
    from Murphy
    option ( MaxRecursion 0 );

A more efficient version uses cross join instead of recursion.

-- Constant across all columns and rows.
declare @Limit as Int = 100000;
with Ten ( Number ) as
  ( select * from ( values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9) ) as Digits( Number ) ),
  TenUp2 ( Number ) as ( select 42 from Ten as L cross join Ten as R ),
  TenUp4 ( Number ) as ( select 42 from TenUp2 as L cross join TenUp2 as R ),
  TenUp8 ( Number ) as ( select 42 from TenUp4 as L cross join TenUp4 as R ),
  Numbers ( Number, A, B ) as ( select top (@Limit) Row_Number() over ( order by ( select NULL ) ),
    GetDate(), GetDate() from TenUp8 )
  select Min( A ) as MinA, Max( A ) as MaxA, Min( B ) as MinB, Max( B ) as MaxB
    from Numbers;

Then again, maybe different instances can drift apart.

-- Fails (and does not generate an execution plan).
declare @A as DateTime = GetDate();
declare @B as DateTime = @A;
declare @Trials as Int = 0;

while @A = @B
  begin
  select @A = GetDate(), @B = GetDate(), @Trials += 1;
  if @Trials % 1000 = 0
    print @Trials;
  end

select @A as A, @B as B, @Trials as Trials;

So you think that's just a set posing as a select and a real select that generates an execution plan would work differently.

-- Fails.
declare @A as DateTime = GetDate();
declare @B as DateTime = @A;
declare @Trials as Int = 0;

while @A = @B
  begin
  select @A = GetDate(), @B = GetDate(), @Trials += 1
    from ( values ( 42 ) ) as PH( A );
  if @Trials % 1000 = 0
    print @Trials;
  end

select @A as A, @B as B, @Trials as Trials;

What if the values come from a table value constructor?

-- Fails.
declare @A as DateTime = GetDate();
declare @B as DateTime = @A;
declare @Trials as Int = 0;

select @A = GetDate(), @B = @A;
while @A = @B
  begin
  select @A = A, @B = B, @Trials += 1
    from ( values ( GetDate(), GetDate() ) ) as PH( A, B )
  if @Trials % 1000 = 0
    print @Trials;
  end

select @A as A, @B as B, @Trials as Trials;

Well, all of the failures come from select statements that assign values to variables. Let's eliminate that by inserting rows into a table and checking them later. (Note: This example doesn't fail when run on SQL Fiddle with SQL Server 2017.)

-- Fails on SQL Server 2008 R2, but not on SQL Server 2017.
declare @Samples as Table ( A DateTime, B DateTime );
declare @Trials as Int = 0;
while @Trials < 100000
  begin
  insert into @Samples ( A, B ) values ( GetDate(), GetDate() )
  set @Trials += 1;
  end

select A, B
  from @Samples
  where A != B;
select Min( A ) as MinA, Max( A ) as MaxA, Min( B ) as MinB, Max( B ) as MaxB
  from @Samples;

Answer 2

I ran both approaches in SQL Server 2016. I did not find any difference. But, as @Cato mentioned in the comments, it is better to declare as a variable and pass the variable, instead of waiting for the optimizer to take care of it.

Approach 1

DECLARE @currentDate DATE = GETDATE()
;WITH CalendarSequence as(
  SELECT @currentDate as RollingDate
  UNION ALL
  SELECT DateAdd(month, -1, RollingDate) as RollingDate
  FROM CalendarSequence
  WHERE DateAdd(month, -1, RollingDate) > Convert(date, '2018-01-01')
)
SELECT * FROM CalendarSequence

Approach 2

;WITH CalendarSequence as(
  SELECT Getdate() as RollingDate
  UNION ALL
  SELECT DateAdd(month, -1, RollingDate) as RollingDate
  FROM CalendarSequence
  WHERE DateAdd(month, -1, RollingDate) > Convert(date, '2018-01-01')
)
SELECT * FROM CalendarSequence

Both of them are having the same execution plan. No changes at all.