CREATE TABLE #Analysis(ID INT IDENTITY,Margin [numeric](21, 6) NULL,Gallons INT,Freight [numeric](21, 6) NULL,AccMargin [numeric](21, 6) NULL)INSERT INTO #Analysis ( Margin,Gallons,Freight)SELECT 0.050220,5022,-30.180000INSERT INTO #Analysis ( Margin,Gallons,Freight)SELECT 0.050220,-5022,318.260000UPDATE #AnalysisSET AccMargin = FLOOR(10000000*(CAST(MArgin AS DECIMAL(22,7))/CAST(Gallons AS DECIMAL(22,7)) + CAST(Freight AS DECIMAL(22,7))/CAST(Gallons AS DECIMAL(22,7))))/10000000 SELECT * FROM #AnalysisDROP TABLE #Analysis

UPDATE dbo.AnalysisSET AccMargin = ((MArgin+Freight)/Gallons)

SELECT SQL_VARIANT_PROPERTY(CONVERT(sql_variant, Margin + Freight), 'basetype'), SQL_VARIANT_PROPERTY(CONVERT(sql_variant, Margin + Freight), 'precision'), SQL_VARIANT_PROPERTY(CONVERT(sql_variant, Margin + Freight), 'scale'), SQL_VARIANT_PROPERTY(CONVERT(sql_variant, Margin/Gallons), 'basetype'), SQL_VARIANT_PROPERTY(CONVERT(sql_variant, Margin/Gallons), 'precision'), SQL_VARIANT_PROPERTY(CONVERT(sql_variant, Margin/Gallons), 'scale'), SQL_VARIANT_PROPERTY(CONVERT(sql_variant, (Margin/Gallons + Freight/Gallons)), 'basetype'), SQL_VARIANT_PROPERTY(CONVERT(sql_variant, (Margin/Gallons + Freight/Gallons)), 'precision'), SQL_VARIANT_PROPERTY(CONVERT(sql_variant, (Margin/Gallons + Freight/Gallons)), 'scale')FROM #Analysis AS A

SUM numeric 22 6Division numeric 32 17Sum of divs numeric 33 17

UPDATE #AnalysisSET AccMargin = ROUND(Margin/Gallons + Freight/Gallons, 6, 1)

ID Margin Gallons Freight AccMargin1 0.050220 5022 -30.180000 -0.0059992 0.050220 -5022 318.260000 -0.063383

CREATE TABLE #Analysis(ID INT IDENTITY,Margin [numeric](21, 6) NULL,Gallons INT,Freight [numeric](21, 6) NULL,AccMargin [numeric](21, 6) NULL)INSERT INTO #Analysis ( Margin,Gallons,Freight)SELECT 0.050220,5022,-30.180000INSERT INTO #Analysis ( Margin,Gallons,Freight)SELECT 0.050220,-5022,318.260000SELECT ID, Margin, Gallons, Freight ,AccMargin1 = FLOOR(10000000*(CAST(MArgin AS DECIMAL(22,7))/CAST(Gallons AS DECIMAL(22,7)) + CAST(Freight AS DECIMAL(22,7))/CAST(Gallons AS DECIMAL(22,7))))/10000000 ,AccMargin2 = FLOOR(10000000*((MArgin + Freight)/Gallons))/10000000 ,AccMargin3 = FLOOR(10000000*(MArgin/Gallons + Freight/Gallons))/10000000 ,AccMargin4 = ROUND(MArgin/Gallons + Freight/Gallons, 6, 1) ,AccMargin5 = ROUND((MArgin + Freight)/Gallons, 6, 1)FROM #Analysis;WITH Tally (n) AS ( SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns a CROSS JOIN sys.all_columns b)INSERT INTO #Analysis (Margin, Gallons, Freight)SELECT RAND(CHECKSUM(NEWID())), CASE gallons WHEN 0 THEN 100 ELSE gallons END ,CHECKSUM(NEWID()) % 500FROM TallyCROSS APPLY (SELECT CHECKSUM(NEWID()) % 10000) a (gallons)DECLARE @BlackHole [numeric](21, 6)PRINT 'AccMargin2'SET STATISTICS TIME ONSELECT @BlackHole = FLOOR(10000000*((MArgin + Freight)/Gallons))/10000000FROM #AnalysisSET STATISTICS TIME OFFPRINT 'AccMargin5'SET STATISTICS TIME ONSELECT @BlackHole = ROUND((MArgin + Freight)/Gallons, 6, 1)FROM #AnalysisSET STATISTICS TIME OFFDROP TABLE #Analysis

AccMargin2 SQL Server Execution Times: CPU time = 1014 ms, elapsed time = 1010 ms.AccMargin5 SQL Server Execution Times: CPU time = 858 ms, elapsed time = 865 ms.

--===== Using the NUMERIC datatypeDECLARE @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;