This might be something most developers are already aware of, but I have been familiar with SQL Server mostly as a DBA, not actively busy with the meaning of data and it therefore came as a complete surprise to me.
Question: How much is $1/$345, rounded at 4 decimals?
Take a calculator and it will tell you: 0.0029.
Take Excel and it will tell you: 0.0029.
Take SQL Server and it will tell you: 0.0028.
Euh… wait a minute… did you say 0.0028? Isn’t that 3-4% off?
Seems SQL Server, when using the MONEY datatype, doesn’t round values, but truncates them instead. See the example below. I think it’s pretty shocking.
declare @m1 money, @m2 money, @r1 decimal(19,4)
declare @d1 decimal(19,4), @d2 decimal(19,4), @r2 decimal(19,4)
declare @f1 float, @f2 float, @r3 float;
set @m1 = 1.00;
set @m2 = 345.00;
set @r1 = @m1/@m2;
set @d1 = 1.00;
set @d2 = 345.00;
set @r2 = @d1/@d2;
set @f1 = 1.00;
set @f2 = 345.00;
set @r3 = @f1/@f2;
select @r1, @r2, @r3;