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.
- Difference between numeric, float and decimal in SQL Server
- 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