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
  • 697
  • 4
  • 12
  • 22
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://docs.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://docs.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://docs.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

1 Answers1

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
  • 697
  • 4
  • 12
  • 22