SQL Server decimal calculations showing odd results
Consider the script below ran in Management Studio on SQLServer 2008:
declare @N1 decimal(38,12)
declare @N2 decimal(38,12)
begin
set @N1=1111223333444455556666
set @N2=1111223333444455556666
select @N1/@N2 --expected result
select @N1*cast(-1 as decimal(38,12))/@N2
select @N1*(-1*(1.0))/@N2
select @N1*(-1.0/@N2)
select 1.0/@N2
end;
The results are:
1.000000
-0.999999
-0.999999
-0.888979
0.000000000000000000000000
Can anyone explain these results? Our problem is our software has to parse user friendly representation of field and number summing into sql friendly script which in turn gives correct and consistent results. We do need to deal with large number and many decimal places, hence using decimal(38,12). We are aware of the need to make the nominator of a division into a decimal number otherwise the result is assumed to be an integer,
ie
select 2/5 results in 0, and select 2.0/5 results in 0.4
We have also noticed that SQLServer doesn't always apply BODMAS calculation ordering. So, is there a set of rules that can be followed to achieve reliable results?
Many thanks
Alex
declare @N1 decimal(38,12)
declare @N2 decimal(38,12)
begin
set @N1=1111223333444455556666
set @N2=1111223333444455556666
select @N1/@N2 --expected result
select @N1*cast(-1 as decimal(38,12))/@N2
select @N1*(-1*(1.0))/@N2
select @N1*(-1.0/@N2)
select 1.0/@N2
end;
The results are:
1.000000
-0.999999
-0.999999
-0.888979
0.000000000000000000000000
Can anyone explain these results? Our problem is our software has to parse user friendly representation of field and number summing into sql friendly script which in turn gives correct and consistent results. We do need to deal with large number and many decimal places, hence using decimal(38,12). We are aware of the need to make the nominator of a division into a decimal number otherwise the result is assumed to be an integer,
ie
select 2/5 results in 0, and select 2.0/5 results in 0.4
We have also noticed that SQLServer doesn't always apply BODMAS calculation ordering. So, is there a set of rules that can be followed to achieve reliable results?
Many thanks
Alex
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Do we need to restrict the numbers used in calculations to so many decimal places and then cast them to a larger number of decimal places? But then the results would be the same. I can't see a way out of this.