SQL 2019 handle decimal difference from SQL 2014

vorakit.l 1

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%.

4 answers

EchoLiu-MSFT 14,581 Reputation points

2021-02-11T09:02:11.367+00:00

Could you provide some examples?
Please sign in to rate this answer.
vorakit.l 1 Reputation point

2021-02-11T09:30:20.897+00:00

First picture show the formula without using AVG to compare the result between SQL 2014 and SQL 2019. The result is equally.
Second picture show the formula with using AVG. The result is difference.

EchoLiu-MSFT 14,581 Reputation points

2021-02-15T06:48:40.163+00:00

Experts have discussed various possible methods, and it is best to provide data for testing.

Echo

Tom Phillips 17,731 Reputation points

2021-02-15T16:29:42.923+00:00

The value in SQL 2019 is CORRECT. The value in SQL 2014 is NOT correct.

There is not a problem in SQL 2019. The problem was in all SQL versions prior to SQL 2016.

Again, I am having trouble finding the reference. However, prior to 2016 the aggregation functions where truncating the decimal places which caused rounding errors. In SQL 2016+, decimal values are properly rounded.

Prior to SQL 2016, AVG(27.8754545) = 27.875454. In versions 2016+ this is properly rounded to 27.875455.

Dan Guzman 9,236 Reputation points

2021-02-15T21:19:40.823+00:00

@Tom Cooper , did you see my answer? It includes the reference to documented behavior.

Erland Sommarskog 111.1K Reputation points MVP

2021-02-15T21:54:44.027+00:00

Tom Cooper? Where did you see in this thread? :-)

I did note your comment, and I was aware of that change when the thread started. Note however, that I seem to get the same results on both SQL 2019 CU9 and SQL 2014 SP3 with the examples vorakiti had. Now, since vorakiti only provided a screen shoot, I may have made an error in my test. But it is telling that vorakiti has SQL 2014 RTM, so it could be an old bug that has been fixed, and which is not covered by any compat level.

Tom Cooper 8,466 Reputation points

2021-02-16T03:21:44.527+00:00

I assume you meant @Tom Phillips . Thanks for the unintentional complement. Being mistaken for Tom Phillips is a great honor.

Dan Guzman 9,236 Reputation points

2021-02-16T11:09:57.027+00:00

Yes I did mean Tom Phillips of course ;-) Thanks to you and Erland for pointing out my mistake.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Tom Phillips 17,731 Reputation points

2021-02-11T14:21:47.807+00:00

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.
Please sign in to rate this answer.
vorakit.l 1 Reputation point

2021-02-12T06:41:56.7+00:00

For avoid this difference from datatype and overflow, we have to cast type as numeric and change the scale.
But this solution can fix only each point. Do you have any solution that can change or fix one time or middle function to avoid missing
points ?
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Erland Sommarskog 111.1K Reputation points MVP

2021-02-11T23:02:51.947+00:00

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.
Please sign in to rate this answer.
vorakit.l 1 Reputation point

2021-02-12T06:42:23.577+00:00

For avoid this difference from datatype and overflow, we have to cast type as numeric and change the scale.
But this solution can fix only each point. Do you have any solution that can change or fix one time or middle function to avoid missing
points ?

Erland Sommarskog 111.1K Reputation points MVP

2021-02-12T22:27:07.483+00:00

I'm afraid I don't get what you mean with "missing point". But instead of posting screenshots, and you post the query as text? It is not possible to copy and paste from a screen shot.l

vorakit.l 1 Reputation point

2021-02-13T03:25:28.627+00:00

It means we have a lot of points in our coding that may occurred this case. If we try to search, fix and test one by one, this may missing some case. So we would like to ask other way around such as fixing or configuration as the core function to avoid some case that we cannot search due to the decimal issue is very small error that hard to find.

Erland Sommarskog 111.1K Reputation points MVP

2021-02-13T10:07:27.187+00:00

What errors? As I noted above, it appears that you have decimal errors in SQL 2014 RTM which have been corrected in SQL 2019 as well as in service packs of SQL 2014.

Again, it could help if you shared repro that we could work with. That is, a script. The screenshot is difficult to work with.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Dan Guzman 9,236 Reputation points

2021-02-13T13:51:49.803+00:00

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.
Please sign in to rate this answer.
Tom Phillips 17,731 Reputation points

2021-02-16T12:28:45.16+00:00

@Dan Guzman Thank you. I made note of the link for future questions on this subject.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

SQL 2019 handle decimal difference from SQL 2014

4 answers

Your answer