SQL Server Rounding issue

Question

I have a problem in data accuracy when I store data in variables/columns.

Consider the following example

DECLARE @w NUMERIC(38, 15) = 0.458441,
        @a NUMERIC(38, 15) = 10.000000,
        @p NUMERIC(38, 15) = 5.000000

select  (1+0.458441)*(1+    10.000000/100)*(1+  5.000000/100) 

Result : 1.68449935500000000000000000 (correct)

SELECT ( 1 + @w ) * ( 1 + @a / 100.0 ) * ( 1 + @p / 100.0 ) 

Result : 1.684499 (incorrect)

Can anyone tell what is the reason for approximation when I store values in variable and how it can be fixed?

Answer 1

First check datatypes:

SELECT '0.458441', system_type_name
FROM sys.dm_exec_describe_first_result_set(N'SELECT  0.458441', NULL, 0) 
UNION ALL   
SELECT '10.000000', system_type_name
FROM sys.dm_exec_describe_first_result_set(N'SELECT  10.000000', NULL, 0)  
UNION ALL   
SELECT '5.000000', system_type_name
FROM sys.dm_exec_describe_first_result_set(N'SELECT  5.000000', NULL, 0);

╔═══════════╦══════════════════╗
║   value   ║ system_type_name ║
╠═══════════╬══════════════════╣
║ 0.458441  ║ numeric(6,6)     ║
║ 10.000000 ║ numeric(8,6)     ║
║ 5.000000  ║ numeric(7,6)     ║
╚═══════════╩══════════════════╝

Query 1:

SELECT (1+0.458441)*(1+10.000000/100)*(1+5.000000/100)
-- 1.684499355

Query 2:

SELECT (1 + @w) * (1 + @a/100.0) * (1 + @p/100.0)
-- 1.684499

After adding casting:

SELECT (1 + CAST(@w AS NUMERIC(8,6))) * 
       (1 + CAST(@a AS NUMERIC(8,6))/100.0) * 
       (1 + CAST(@p AS NUMERIC(8,6))/100.0)
-- 1.684499355

LiveDemo

Why it is the case: related article