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

by

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

More Related Contents:

  1. Multiple INSERT statements vs. single INSERT with multiple VALUES
  2. I dont know sql syntax for my table [closed]
  3. How do I obtain a Query Execution Plan in SQL Server?
  4. CROSS JOIN vs INNER JOIN in SQL
  5. Unpivot with column name
  6. How to generate a range of numbers between two numbers?
  7. SQL JOIN vs IN performance?
  8. SET versus SELECT when assigning variables?
  9. Convert varchar into datetime in SQL Server
  10. Generate Dates between date ranges
  11. How to print VARCHAR(MAX) using Print Statement?
  12. Check if a row exists, otherwise insert
  13. How to update Identity Column in SQL Server?
  14. Common Table Expression, why semicolon?
  15. T-SQL split string based on delimiter
  16. Concatenate values based on ID
  17. Counting DISTINCT over multiple columns
  18. Splitting delimited values in a SQL column into multiple rows
  19. Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)
  20. TransactSQL to run another TransactSQL script
  21. Can an INNER JOIN offer better performance than EXISTS
  22. Stored procedure that exports data into csv files only exports to one file
  23. How do I prevent a database trigger from recursing?
  24. TSQL – How to use GO inside of a BEGIN .. END block?
  25. Using IF ELSE statement based on Count to execute different Insert statements
  26. How to combine results of two queries into a single dataset
  27. SQL take just the numeric values from a varchar
  28. SQL Server SELECT INTO @variable?
  29. Using CASE Statement inside IN Clause
  30. SQL Server: +(unary) operator on non-numeric Strings

Leave a Comment