MS SQL STDEV retnning in correct values in same query.

Question

Hello All

"Microsoft SQL Server 2014 - 12.0.2000.8 (X64) "

We have query to calculate standard deviation. STDEV(...), for same group of values in one query, its calculating different values.

Reference to below link we have issue in "SQL Server 2014" for calculating STDEVX and STDEVP, does this issue also for STDEV(....) calculation ?

https://support.microsoft.com/en-us/topic/kb3147297-fix-stdevx-p-returns-an-incorrect-result-when-you-calculate-the-standard-deviation-in-sql-server-2014-b764b5d2-a7f3-dbb7-7c03-b6b6134c8e3b

We tried with different version 2016, giving same results.

Answer 1

You are running the RTM version of SQL 2014. The first thing you need to do is update to a current revision level and retest.

https://learn.microsoft.com/en-US/troubleshoot/sql/general/determine-version-edition-update-level#sql-server-2014

Answer 2

As Tom says, you are behind on the service pack level. You should have Service Pack 3, Cumulative Update 4. And given that KB article, that may fix your problem.

Then again, if you see the same thing on SQL 2016, maybe not. STDEV is a floating-point calculation, and if you have a degenerate case with no variation, there may be flutter that results in an unexpected value - but which after all is close to the actual answer.

Answer 3

Thank you Tom for your reply.