4 ms·

what database server evaluates now() per row?

• rileymat2 4y ago
  That seems like it would cause a lot of problems, other than the optimization.
• magicalhippo 4y ago
  That's how my colleague described it to me, seems he misspoke or I misremembered. I checked the documentation and it says now() is evaluated at most once per request. The point was now() and friends prevents optimization on the server we use, because they're not treated as constants.
  • rileymat2 4y ago
    It depends on context, for example this is true for SQL server if you have a filtered index based on datetime. If you use a constant or set a @variable ahead of time, it will use the filtered index, but if you just use =now() equivalent (or in SQL server GETDATE()) it will not. (You may also need to use OPTION(Recompile))
• teej 4y ago
  In Postgres terminology, all functions have a volatility classification: IMMUTABLE (input X will always return y forever), STABLE (input x will always return y for the current statement) and VOLATILE (input x can return anything). That classification impacts how the query gets planned and optimized. All the dbs I know have some version of this. now() should be STABLE in pretty much every db, but not every db gracefully handles STABLE function calls in predicates. You probably know all this but figured I'd share the database jargon for everyone else.
• deleted 4y ago
  [deleted]