How to fix power function issue while changing db compatibility level change

narmatha pitchaimani 0 Reputation points
2023-09-11T07:43:28.2133333+00:00

We are using sql server 2019 server with db 110 compatability. Currently we are changing the DB compatability level from 110 to 150.After changing the compatability level to 150 , we have noticed the breakage there in the power of function and its behaving wired manner. For example, In 110 compatability the power(10,23) returning result as a100000000000000010000000 and in 150 compatability the result as 1000000000000000008388608.Could you please let us know why its behaving this manner on both compatability. I am expecting the result should match on both compatability? And also, If we are not using inbuilt in power function ,if we muliply manually 10 ^24 function then the result should be 100000000000000000000000 correct?That means the power function working weired manner on both compatability else do we have nay lofic behing these?So.eone please help on this

Azure SQL Database
Azure SQL Edge
Azure SQL Edge
An Azure service that provides a small-footprint, edge-optimized data engine with built-in artificial intelligence. Previously known as Azure SQL Database Edge.
45 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,755 questions
SQL Server Migration Assistant
SQL Server Migration Assistant
A Microsoft tool designed to automate database migration to SQL Server from Access, DB2, MySQL, Oracle, and SAP ASE.
495 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Dan Guzman 9,211 Reputation points
    2023-09-11T10:08:25.4933333+00:00

    Improved accuracy of certain numeric operations was introduced with SQL Server 2016 which may result in different value. This breaking change behavior is gated by the database compatibility level so one can use the legacy behavior when needed until the problem code/data can be remediated. Compatibility level 110 (SQL 2012) or less.

    0 comments
  2. Erland Sommarskog 101.4K Reputation points MVP
    2023-09-11T21:46:34.2566667+00:00

    It's not clear to me why you think 100000000000000008388608 is weird, but 100000000000000010000000 is not. After all, the former value is slightly closer to the exact result.

    I don't think SQL Server is geared for this extreme level of precision. The decimal data type has a max precision of 38, and the power function has to deal with all sorts of combination. I would not be surprised if the calculation uses double (i.e. float) on the inside.

    If you have requirements for exact numbers this long, you may have to implement your own functions using the CLR.

    0 comments