SQL 2019 handle decimal difference from SQL 2014

vorakit.l 1 Reputation point

We are in the upgrade project from SQL 2014 to SQL 2019. We are facing the issue when our decimal calculation difference between SQL 2014 and SQL 2019.

From our test result SQL 2019 function AVG can handle only 6 decimal, if no datatype cast. But SQL 2014 can handle more decimal digit in the same method.

We conclude this issue as 2 root causes.
1.Result datatype may be difference between SQL 2014 and SQL 2019 when use function AVG without cast type.
2.Field overflow handle difference between SQL 2014 and SQL 2019. SQL 2014 handle last decimal at digit 6th without rounding anything. But SQL 2019 will use decimal digit 7th to rounding digit 6th as last digit before update value.

From our problems above, can we fix or use any middle function as one time fixing and apply all to handle decimal issue ? Due to there are a lot of points in our code that impact from above issue and we may not search 100%.

A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,456 questions
0 comments No comments
{count} votes

4 answers

Sort by: Most helpful
  1. EchoLiu-MSFT 14,561 Reputation points

    Could you provide some examples?

  2. Tom Phillips 17,686 Reputation points

    There was a problem with decimal/floating point values in previous versions of SQL Server which was fixed in SQL 2016+. I cannot find the reference right now.

    The value in SQL 2019 is correct. The previous version was not correct.

  3. Erland Sommarskog 92,961 Reputation points

    As far as I can tell, the result you have from SQL 2014 is incorrect. The result cannot end in 4.

    Furthermore, when I run your expression on SQL 2014, I get the correct result and the not the incorrect result that you get. However, I notice that you have the RTM version of SQL 2014, so it may be that there was a bug in SQL 2014 which was fixed in a service pack.

  4. Dan Guzman 8,591 Reputation points

    SQL Server 2016 introduced improvements in data type conversions and several other operations that provide more accurate results. The behavior is gated by the database compatibility level. Consequently, you could change the database compatibility level down to SQL 2014 (120) so that your tests comparing the results of SQL 2014 versus 2019 succeed.

    That being said, I suggest you retain the native SQL 2019 compatibility level unless you have another reason to do otherwise. End users would likely prefer the more accurate and correct results. Lower database compatibility levels are intended only as a temporary measure to facilitate fast migration to a newer version by retaining legacy behavior, avoiding changes that may break applications vulnerable to certain changes.

    Different results do not indicate something is broken or a fix is needed. The SQL 2014 results for these calculations is arguably incorrect so tests should assert the SQL 2019 values.