Sergiy (3/4/2013)
Not sure such a change in the formula will add any accuracy.SUM will keep the 6 digits precision, while division will use implicit conversion to FLOAT before and implicit conversion to decimal with higher procision after.
So, there is no need in an explicit expanding, it will be done behind the scene:
Wow! You know me... I had to try it and you're absolutely correct. I don't know why I thought SQL Server was less capable than that. I took a fringe case to prove what you say is correct. Life just became a whole lot easier. Thanks, Sergiy, and nice proof!
Here are the simple tests I did using a fringe case to prove what Sergiy said. I haven't tested to see if or when getting rid of the extra division operator provides a real return on performance but the math works out just like he said.
--===== Using the NUMERIC datatype
DECLARE @Margin NUMERIC (21,6),
@Gallons INT,
@Freight NUMERIC (21,6)
;
SELECT @Margin = 0.000001,
@Gallons = 2,
@Freight = 0.000001
;
SELECT @Margin/@Gallons + @Freight/@Gallons;
SELECT (@Margin + @Freight) / @Gallons;
SELECT @Margin/@Gallons, @Freight/@Gallons;
GO
--===== Using the DECIMAL datatype (just to make sure BOL was actually correct)
DECLARE @Margin DECIMAL (21,6),
@Gallons INT,
@Freight DECIMAL (21,6)
;
SELECT @Margin = 0.000001,
@Gallons = 2,
@Freight = 0.000001
;
SELECT @Margin/@Gallons + @Freight/@Gallons;
SELECT (@Margin + @Freight) / @Gallons;
SELECT @Margin/@Gallons, @Freight/@Gallons;
--Jeff Moden
Change is inevitable... Change for the better is not.