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

[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.

One reason to use a variable with getdate() or functions like suser_sname() is a huge performance difference if you are inserting rows, or if you are doing a GROUP BY. You will notice this if you insert large amount of rows.

I suffered this myself migrating 300GB of data to several tables.

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.

I was testing on a couple of stored procedures using the GETDATE() function as a variable within an SP and I was having increase on IO reads and execution time due to the fact that query optimizer does not know what's the value to operate read this Stored Procedure Execution with Parameters, Variables, and Literals , with that said you can use the GETDATE() function in every single part of the SP as @Gordon Linoff mentioned its value does not change during execution or in order to avoid/remove the thinking that the value might change I did create a parameters this way:

CREATE PROC TestGetdate
(
@CurrentDate DATETIME = NULL
)
AS
SET CurrentDate  = GETDATE()

..... and then use the parameters as you see fit, you'll see good results

Any comments or suggestions are welcome.