Click here to Skip to main content
15,893,486 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
HI,

We are facing a problem when we use system.math.pow function. Upto 2 POW 56 its working fine. I am comparing the result with sql server POW(2.0,56).

From Pow of 57 onwards,when comaparing c# and Sql Server result we are getting different values in C# and Sql Server

Example:

C#
(long)system.math.pow(2,57)

Result - 144115188075855872

SQL Server

Select cast(POW(2.0,57) as bigint)

Result - 144115188075855870


Regards,
Senthil S
Posted
Updated 9-Feb-12 1:51am
v2

You can be sure in a right result only if this is integer value. The type System.UInt64 is big enough, just because 57 < 64, but not System.UInt32 (57 > 32).

Using Pow function for powers of 2 is just silly. You need to shift 1 to the left using << operator, but for System.UInt64 this is not so easy because this operator is only defined for int. You can do it very simply using the class System.BitConverter, please see:
http://msdn.microsoft.com/en-us/library/system.bitconverter.aspx[^].

Here is how to get an exact answer:
C#
static System.UInt64 PowerOfTwo(byte power) {
    byte[] bytes = new byte[sizeof(System.UInt64)];
    int bitShift = power % 8;
    int byteIndex = power / 8;
    bytes[byteIndex] = (byte)(1 << bitShift);
    return System.BitConverter.ToUInt64(bytes, 0);
}


Testing… the answer is 144115188075855872; your C# result was correct, SQL Server is a liar!

If you need bigger values, your only chance is the type System.Numerics.BigInteger; please see:
http://msdn.microsoft.com/en-us/library/system.numerics.biginteger.aspx[^].

—SA
 
Share this answer
 
v7
Comments
senthil sennu 10-Feb-12 7:28am    
I want it to be same in c# and SQL , so wat logic can be followed using power function to keep it same, because from 0-56 its is same why from 57 it has different value in sql server, can we rectify it in sql server?
Sergey Alexandrovich Kryukov 11-Feb-12 5:22am    
I don't think you can write such code in SQL; well, too bad. I'm not sure you have to...
--SA
Sergey Alexandrovich Kryukov 11-Feb-12 5:26am    
Sorry, I've removed the text of your comment you added to my answer, but not because I don't like it but just because it is the same, and the form of comment is exactly right thing for this purpose.

Regards,
--SA
Espen Harlinn 11-Feb-12 5:12am    
5'ed!
Sergey Alexandrovich Kryukov 11-Feb-12 5:22am    
Thank you, Espen.
--SA
If they are different, then they are different. They are being calculated in two places. Have you sat down to work out which is correct ?
 
Share this answer
 
Comments
senthil sennu 9-Feb-12 8:04am    
Though they are calculated in two places, the result should come same for POW(57) Right?
[no name] 9-Feb-12 9:01am    
Not right. In an ideal world variables could hold values of unlimited length and perfect precision, perhaps even calculate PI to the last digit. In reality we are stuck with datatypes limited to a few bytes, so they can obviously only store values within certain limits.

On top of that, the algorithms may reach those limits during the calculation, leading to all kinds of rounding or overflow errors, even if the correct result may still have fit the datatype.
Christian Graus 9-Feb-12 8:07am    
In theory. My point is merely that if the two algorithms give a different result, there's almost certainly nothing you can do to change that.
senthil sennu 10-Feb-12 7:27am    
from 0- 56 both c# and sql server has correct value but from 57 c# has correct value but sql server has wrong value why is it so, as per my logic i want it to be same
Christian Graus 10-Feb-12 7:29am    
You've been told the reason. It's probably a rounding error. If you can get the source code of SQL Server, perhaps you can 'fix' it. Or you can rewrite your code so it's calculated in one place and stored in the other.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900