Why is the LAG function in T-SQL not deterministic?

Question

I am trying to use LAG in T-SQL to compute some lagging functions. I was a little worried when the LAG page says that this function is not deterministic. The manual page on the determinant of the function says that “specifying the ORDER BY clause in the query does not change the determinism of the function used in this query”, However, I do not understand why the LAG will return different results under the same conditions. If so, why should people use this? Maybe I do not interpret "determinism" correctly? Thank you

Answer 1

In mathematics and physics, a deterministic system is a system in which randomness is not related to the development of future states of the system. Thus, a deterministic model always produces the same output from a given initial state or initial state. https://en.wikipedia.org/wiki/Deterministic_system

The LAG function itself is not deterministic, since its results may vary depending on the state of the data, Eric is correct. In some data models and when used correctly, they can be deterministic (for example, if you order with a numeric key in your backlog), but the definition of a function is not deterministic in itself.

Make sense?

Answer 2

MSDN , , (INSERT, DELETE, UPDATE).

. , , . LAG LEAD . , AVG , .