0

I want to use getdate() function 3-4 times in my single query for validation check. But I want that everytime I anticipate to get current datetime in a single query execution I get the same date at all 3-4 places. Not technically computers are that fast that 99.9% times I will get the same datetime at all places in query. But theoretically it may lead to bug. So how can cache that getdate return by calling it once and use that cached values in query.

But to add, I want to write such statement in check constraint, so I cant declare local variables, or any such thing.

Cellman
  • 119
  • 1
  • 9
  • You can create a scalar function to check all the dates and have the date as a local there. At least this would be a good idea in a general purpose programming language, not sure if this is a good practice in SQL context though. – Zdeněk Jelínek May 05 '18 at 08:40
  • Wow, I didn't know that. Following the post from Martin Smith https://stackoverflow.com/a/6043910/2527905 I tried it with multiple versions and SQL Server might actually return different values when executed multiple times. – dnoeth May 05 '18 at 09:20
  • Even when I used Standard SQL `current_timestamp`, so SQL Server is not following the rules, which require that it must be the same value. At least since SQL:92: *If an SQL-statement generally contains more than one reference to one or more s, then all such ref- erences are effectively evaluated simultaneously. The time of evaluation of the during the execution of the SQL-statement is implementation-dependent.* – dnoeth May 05 '18 at 09:21

1 Answers1

0

SQL Server has the concept of run-time constant functions. The best way to describe these is that the first thing the execution engine does is pull the function references out from the query plan and execute each such function once per query.

Note that the function references appear to be column-based. So different columns can have different values, but different rows should have the same value within a column.

The two most common functions in this category are getdate() and rand(). Ironically, I find that this is a good thing for getdate(), but a bad thing for rand() (what kind of random number generator always returns the same value?).

For some reason, I can't find the actual documentation on run-time constant functions. But here are some respected blog posts that explain the matter:

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • How does this help the OP when he wants to use multiple `getdate()` in a check constraint and they might return different constant values? – dnoeth May 05 '18 at 14:41
  • @dnoeth . . . Nothing that I've seen on run-time constant functions distinguishes between `insert`/`update` statements and `select` statements. I will admit that the documentation is a bit spotty. But the optimization is to filter these functions out of the execution plan at the beginning of execution, so they are run only once. That would apply to `insert` and `update` as well. – Gordon Linoff May 05 '18 at 14:59
  • When you check Martin Smith's post https://stackoverflow.com/a/6043910/2527905 you will see that each `getdate` is evaluated individually and thus might lead to different values within the same statement. Still true in SS2017, but totally wrong according to Standard SQL, of course :-) – dnoeth May 05 '18 at 15:18
  • @dnoeth . . . The "sameness" is per *query* not per programming block or script. – Gordon Linoff May 05 '18 at 22:22
  • No, it's not per query, it's per function call. See Martin Smith's post, it's still true for SS2017 and for all variations like `SYSDATETIME` or `CURRENT_TIMESTAMP` (I was hoping that at least the Standard SQL version works correctly) – dnoeth May 06 '18 at 08:36