I'm trying to use LAG in T-SQL to compute some lagging features. I got a little worried when the LAG reference page says that this function is non-deterministic. The reference page on function determinism says that "specifying an ORDER BY clause in a query does not change the determinism of a function that used in that query". However, I don't see why LAG would return different results under the same condition. If it does, why would people use it? Maybe I'm not interpreting "determinism" correctly? Thanks!
In mathematics and physics, a deterministic system is a system in which no randomness is involved in the development of future states of the system. A deterministic model will thus always produce the same output from a given starting condition or initial state. https://en.wikipedia.org/wiki/Deterministic_system
The LAG function itself is not deterministic because its results can change depending on data state, Eric is correct. In some data models, and when applied correctly, it can be deterministic (like if you order by numeric key in your lag) but the function definition by itself is not deterministic.
Make sense?