4

We have changed the SQL Server compatibility from 100 to 130 the below code in our stored procedure behaves differently:

DECLARE @i decimal(4, 0) = 1,
        @j decimal(4, 1) = 0.5

SELECT SUM(@i) - SUM(@j)

Result of the select:

  • Compatibility Level 100: 0
  • Compatibility Level 130: 1

We are not sure how many calculation we have in our code like this.

Is there any setting in the SQL Server database we can make to work same as compatibility level 100?

timnavigate
  • 741
  • 4
  • 12
  • 23
user3410577
  • 41
  • 3
  • I'm pretty sure the issue is `sum()`, which returns `decimal(38,0)` and `decimal(38,1)` for the two types. Something subtle changed between the versions; the more shocking thing is that the result is not 0.5, the correct answer. – Gordon Linoff Jun 09 '20 at 22:10
  • 1
    I suspect the issue is `@i decimal(4,0)` with the zero meaning no decimal places. If you change this to `@i decimal(4,1)` you will find the correct answer. – Kane Jun 09 '20 at 22:19
  • 2
    My problem is why same set of code behaves different in compatibility and how to fix that. Fixing the code can be done. But how to fix everywhere without modifying all the places – user3410577 Jun 10 '20 at 00:34
  • 2
    Without code modification, your only recourse is the lower compatibility level. The data type result of adding decimal(38,0) and decimal(38,1) is decimal(38,0). The scale is reduced to zero per [decimal addition documentation](https://learn.microsoft.com/en-us/sql/t-sql/data-types/precision-scale-and-length-transact-sql). – Dan Guzman Jun 10 '20 at 02:30
  • 2
    There is a tool from Microsoft called the [Data Migration Assistant](https://learn.microsoft.com/en-us/sql/dma/dma-overview). It can help you uncover issues like this. Changing comparability levels may have introduced other changes, to your system. – David Rushton Jun 10 '20 at 07:17
  • I used Data Migration Assistant. For this issue there are no recommendations around data type so there is no use with this tool. – user3410577 Jun 10 '20 at 16:06
  • @user3410577, the migration assistant is a good start but it will not detect all breaking changes. You still need to review the [breaking changes documentation](https://learn.microsoft.com/en-us/sql/database-engine/breaking-changes-to-database-engine-features-in-sql-server-2016) for each version from the old to new compatibility level. – Dan Guzman Jun 13 '20 at 19:01

3 Answers3

1

In previous versions of SQL Server, the "scale" of the result was not following the rules. This was corrected in a later version (I don't remember which version changed).

No, there is no way to change it back.

https://learn.microsoft.com/en-us/sql/t-sql/data-types/precision-scale-and-length-transact-sql?view=sql-server-ver16

Tom Phillips
  • 11
  • 1
0

Let's try to use suitable data types: https://stackoverflow.com/a/7158770/5309660

DECLARE @i decimal(4, 0) = 1,
        @j decimal(4, 1) = 0.5

SELECT CAST(SUM(@i) AS REAL) - CAST(SUM(@j) AS REAL)
timnavigate
  • 741
  • 4
  • 12
  • 23
0

To simplify things a bit the gist of the issue is that Compatibility Level 120

DECLARE @A decimal(38,0) = 1, 
        @B decimal(38,1) = 0.5

SELECT @A - @B /*Returns 0*/

Compatibility Level 130

DECLARE @A decimal(38,0) = 1, 
        @B decimal(38,1) = 0.5

SELECT @A - @B /*Returns 1*/

The datatype of the result is decimal(38,0) in both cases.

Your question is not asking how to get the mathematically correct result of 0.5. You are just asking how to get the compat 120 behaviour.

You can't without setting that compatibility level I'm afraid.

Note in both compat levels

SELECT CONVERT(decimal(38,0), 0.5)

Returns 1 so this should really be the "expected" result in both cases.

In the 100 case I assume what it actually does is convert the operand first. i.e. as below

SELECT @A - CONVERT(decimal(38,0), @B)

I think the only time it will make a difference in this case is when one of the operands has decimal .5 and you are subtracting it. If @B is 0.6 for example it makes no odds if this gets rounded up to the closest integer before the subtraction or the result of the subtraction is rounded down to the closest integer after it.

The SQL Server 2016 breaking changes does call out

Under database compatibility level 130, operations that perform implicit conversions between certain numeric and datetime data types show improved accuracy and can result in different converted values.

Although it doesn't explicitly mention this case when clicking through.

Is there any setting in the SQL Server database we can make to work same as compatibility level 100?

Only by setting the compatibility level. It is exactly this sort of thing that the compatibility level mechanism is for (to give you time to identify and adjust any affected code)

Martin Smith
  • 438,706
  • 87
  • 741
  • 845