When using GETDATE() in many places, is it better to use a variable?

Question

By better, I mean does it improve performance by some non-marginal amount?

That is to say, each time I call GETDATE(), what amount of work does the server do to return that value?

If I'm using GETDATE() in many places in a stored procedure, should I instead be creating a variable to store the date of the transaction?

declare @transDate datetime = GETDATE() 

Bench-marking data would be fantastic.

EDIT I want to clarify: I'm interested mainly in the actual performance differences between these two possibilities, and whether or not it is significant.

Answer 1

[NOTE: If you are going to downvote this answer, please leave a comment explaining why. It has already been downvoted many times, and finally ypercube (thank you) explained at least one reason why. I can't remove the answer because it is accepted, so you might as well help to improve it.]

According to this exchange on Microsoft, GETDATE() switched from being constant within a query to non-deterministic in SQL Server 2005. In retrospect, I don't think that is accurate. I think it was completely non-deterministic prior to SQL Server 2005 and then hacked into something called "non-deterministic runtime constant" since SQL Server 2005". The later phrase really seems to mean "constant within a query".

(And GETDATE() is defined as unambiguously and proudly non-deterministic, with no qualifiers.)

Alas, in SQL Server, non-deterministic does not mean that a function is evaluated for every row. SQL Server really does make this needlessly complicated and ambiguous with very little documentation on the subject.

In practice the function call is evaluated when the query is running rather than once when the query is compiled and its value changes each time it is called. In practice, GETDATE() is only evaluated once for each expression where it is used -- at execution time rather than compile time. However, Microsoft puts rand() and getdate() into a special category, called non-deterministic runtime constant functions. By contrast, Postgres doesn't jump through such hoops, it just calls functions that have a constant value when executed as "stable".

Despite Martin Smith's comment, SQL Server documentation is simply not explicit on this matter -- GETDATE() is described as both "nondeterministic" and "non-deterministic runtime constant", but that term isn't really explained. The one place I have found the term , for instance, the very next lines in the documentation say not to use nondeterministic functions in subqueries. That would be silly advice for "nondeterministic runtime constant".

I would suggest using a variable with a constant even within a query, so you have a consistent value. This also makes the intention quite clear: You want a single value inside the query. Within a single query, you can do something like:

select . . .  from (select getdate() as now) params cross join      . . .  

Actually, this is a suggestion that should evaluate only once in the query, but there might be exceptions. Confusion arises because getdate() returns the same value on all different rows -- but it can return different values in different columns. Each expression with getdate() is evaluated independently. This is obvious if you run:

select rand(), rand() from (values (1), (2), (3)) v(x); 

Within a stored procedure, you would want to have a single value in a variable. What happens if the stored procedure is run as midnight passes by, and the date changes? What impact does that have on the results?

As for performance, my guess is that the date/time lookup is minimal and for a query occurs once per expression as the query starts to run. This should not really a performance issue, but more of a code-consistency issue.

Answer 2

My suggestion would be to use a variable mainly because if you have a long-running process, the GetDate() value might be different between calls.

Unless you are only using the Date part of GetDate() then you will be sure you are always using the same value.