1

I have an application that user define formula based on other columns (e.g c=(a-b)*100/a ) then the application create SQL update query to calculate result (Types of all columns are NUMERIC). When the formula contains multiplication and division the result is not accurate. I know SQL Server truncates the scale to fit the result in multiplication and division.

  1. Difference between numeric, float and decimal in SQL Server
  2. https://blogs.msdn.microsoft.com/sqlprogrammability/2006/03/29/multiplication-and-division-with-numerics/

But I am confused with below test. When I use variable or use formula in Select query the result is accurate but when I use formula in Update query the result is inaccurate!

--USE NUMERIC
CREATE TABLE TblTest
(
    a [numeric](18, 8) NULL,
    b [numeric](28, 15) NULL,
    c [numeric](28, 15) NULL    
) ON [PRIMARY]

INSERT TblTest (a, b)
VALUES (158.62700000, 155.000000000000000)

UPDATE TBlTest
SET c = (a - b) * 100 / a

SELECT a, b, c, (a-b) * 100 / a AS c2 
FROM TBlTest 

Result: c=2.286495000000000 !!! and c2=2.286495993746

DECLARE @a AS NUMERIC(18, 8) = 158.62700000
DECLARE @b AS NUMERIC(28, 15) = 155.000000000000000
DECLARE @c AS NUMERIC(28, 15)

SELECT (@a - @b) * 100 / @a    

Result: 2.286495993746

SELECT @c = (@a - @b) * 100 / @a 

Result: 2.286495993746000

--USE FLOAT
CREATE TABLE TblTest2
(   
    a FLOAT(53) NULL,
    b FLOAT(53) NULL,
    c FLOAT(53) NULL    
) ON [PRIMARY]

INSERT TblTest2 (a, b)
VALUES (158.62700000, 155.000000000000000)

UPDATE TBlTest2
SET c = (a - b) * 100 / a

SELECT a, b, c, (a - b) * 100 / a AS c2 
FROM TBlTest2 

Result: 2.28649599374634

Community
  • 1
  • 1
Davidd
  • 31
  • 4
  • just to clarify your question, please state what the correct value is and the incorrect value – Tanner Oct 18 '16 at 08:54
  • 1
    SQL Server version? When I try your first example with 2012 SP1, I get the same value for `c` and `c2` (different scale though, which I suppose is interesting) – AakashM Oct 18 '16 at 09:02
  • I get the same result as @AakashM – Tanner Oct 18 '16 at 09:07
  • SQL Server 2008 R2. Correct Value :2.286495993746 Incorrect Value :2.286495000000000 – Davidd Oct 18 '16 at 10:05

0 Answers0