Different computation in C# and SQLSERVER

Question

In C# the result of 1647*0.15=247.04999999999998

In SQLServer the same calculation is: 247,05

How can I set SQLSERVER to give return same as in C#. I have tried several cast and number type, however, I could not change the result. The relevant SQLSERVER Code is below:

select cast(1647 as decimal(18,6))*cast(0.15 as decimal(18,6))

select cast(1647 as float)*cast(0.15 as float)

select cast(1647 as decimal(18,10))*cast(0.15 as decimal(18,10))

select cast(1647 as real)*cast(0.15 as real)

try to use decimal for calculate in c#? like `1674m * 0.15m` — kcwu, Dec 19 '16 at 08:17
"I have tried several cast and number type.." Can you provide an example of what you've tried, and how? — Mackan, Dec 19 '16 at 08:17
Looks like essentially the same result just formatted by two different conversions from a number to a string. You would need to add more information as to which types in C# (supposedly `double`) and SQL Server you use and whether other operations with the given number result into differences as well. — Andrew Sklyarevsky, Dec 19 '16 at 08:17
many environments have own rules to the rounding of floating points. This is example — Jacek Cz, Dec 19 '16 at 08:17
Related: http://stackoverflow.com/questions/1420752/is-double-multiplication-broken-in-net — Pierre-Loup Pagniez, Dec 19 '16 at 08:18
I have updated my question and added the relevant sqlserver codes. Please take a glance at it. Unfortunately nothing have changed. — Suat Atan PhD, Dec 19 '16 at 08:26
@SuatAtan - When I complete `1647*0.15` in C# I get `247.05`. Can you post the C# code that fails? Can you also let us know the results of the SQL Server code? — Enigmativity, Dec 19 '16 at 11:17

score 4 · Answer 1 · answered Dec 19 '16 at 08:23

4

Most likely the C# computation is done within the double data type of C# while in SQL Server the computation is done withing the DECIMAL data type of SQL Server, or a different data type like SQL Server NUMERIC or REAL.

answered Dec 19 '16 at 08:23

Codor

17,447
9
29
56

Enigmativity · Answer 2 · 2016-12-19T11:19:27.333

The issue here is that different systems use different approximations for their representation of real numbers.

A double or decimal in C# isn't necessarily represented in the same way on another system.

The only guarantee that you have is when you perform a computation on one system using a specific representation of real numbers that that system uses that you'll get the same result.

You probably will get the same result if you use a type on two different system if that type is implemented correctly with a known standard - such as IEE Floating Point.

However, even on the same system with the same types things can go wrong. Take these two mathematically identical functions:

When computed (on a system that keeps only the four most significant decimal digits) they can produce very different results depending on the input:

You are dealing with approximations. You will gets errors. It's best that you figure out a way to compute the results that you need in such a way that the errors don't matter (so much).

score 0 · Answer 3 · edited May 23 '17 at 10:30

0

246.05 is correct. So you are essentially asking how can you produce the same wrong answer on two different systems. I am not sure that is always possible.

If you wish to maintain identical computations across systems, you should avoid floating point types like double or real. Only fixed point data types guarantee the same answer every time.

See also Advantages and disadvantages of floating point and fixed point representations.

edited May 23 '17 at 10:30

Community

1
1

answered Dec 19 '16 at 08:50

John Wu

50,556
8
44
80

Agree, specially when is next SQL layers are added (server itself, drivers on client) – Jacek Cz Dec 19 '16 at 09:09
It is right however, the old application which I deal with have written by c# and I have to write the same code in SQLSERVER (via functions and stored procedures). The client requires exact results with old application. – Suat Atan PhD Dec 19 '16 at 09:15
Have you considered writing your stored procedures in c# using [SQL Server CLR Integration](https://msdn.microsoft.com/en-us/library/ms254498(v=vs.110).aspx)? Seems that would solve the problem and also save you a lot of work. – John Wu Dec 19 '16 at 09:28
This isn't true - "Only fixed point data types guarantee the same answer every time." You will always get a deterministic (i.e. same) answer when you use `double` or `float`. It's just that different implementations of **both** fixed point and also floating point numbers might give different answers. – Enigmativity Dec 19 '16 at 10:48
@JohnWu - Can you also point to where in the linked article it supports the answers that you gave? – Enigmativity Dec 19 '16 at 10:51
It looks like the best way is trying SQL Server CLR integration due to the ambiguity of this problem. – Suat Atan PhD Dec 19 '16 at 13:56

score 0 · Answer 4 · edited May 23 '17 at 12:26

Thanks to John Wu I have tried to use CLR Integration. To get C# like multiplying I have developed a multiplication function 'clrcarpma' like below on the C# side:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString clrCarpma(double x, double y)
    {
        double z = x * y;
        string sz = z.ToString("R");
        return new SqlString(sz);
    }
}

Then I have published it to my SQL Server Database. Then I executed it and get the result which I have wanted.

select dbo.clrCarpma(1647,0.15)

and I have get the result 247,04999999999998.

Different computation in C# and SQLSERVER

4 Answers4