0

The documentation pages of the STDEV / STDEVP functions in SQL Server says the following.

STDEV/STDEVP is a deterministic function when used without the OVER and ORDER BY clauses. It is nondeterministic when specified with the OVER and ORDER BY clauses.

Questions:

  1. Why are the STDEV / STDEVP nondeterministic when used with OVER and ORDER BY?

  2. Can anyone provide a few good examples illustrating this point?

https://msdn.microsoft.com/en-us/library/ms190474.aspx

https://msdn.microsoft.com/en-us/library/ms176080.aspx

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
peter.petrov
  • 38,363
  • 16
  • 94
  • 159
  • I figured out the difference between the two (STDEVP should be used for the population while STDEV should be used for samples from the population). So I am just wondering about that non-deterministic behavior now. – peter.petrov Nov 22 '15 at 20:38
  • Probably you should read http://dba.stackexchange.com/questions/77639/are-rank-and-dense-rank-deterministic-or-non-deterministic – Lukasz Szozda Nov 22 '15 at 21:01
  • @lad2025 I just read it quickly. Hm... So... in the normal/human sense (and not in the query optimizer's sense), the functions are deterministic after all? – peter.petrov Nov 22 '15 at 21:07
  • I cannot answer it now. But simple `SUM` without `OVER` can be undeterministic too when used with `FLOAT/REAL/DOUBLE`. See http://stackoverflow.com/questions/33322778/avg-of-float-inconsistency/33323021#33323021 – Lukasz Szozda Nov 22 '15 at 21:13

0 Answers0