[ACCEPTED]-When using GETDATE() in many places, is it better to use a variable?-tsql

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Any comments or suggestions 1 are welcome.

I used

WHERE ActualDateShipped+30 > dbo.Today()

in combination with function below. Brought 3 my query time from 13 seconds to 2 seconds. No 2 prior answers in this post helped this problem 1 in SQL 2008/R2.

CREATE FUNCTION [dbo].[Today]()

    RETURNS date
    AS
    BEGIN

        DECLARE @today date = getdate()

        RETURN @today
    End