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

Question

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

Answer 1

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.

Answer 2

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.