StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Just as addition of the float type is inaccurate, multiplication of the decimal types can be inaccurate (or cause inaccuracy) if you exceed the precision. See <a href="http://msdn.microsoft.com/en-us/library/ms191530%28SQL.90%29.aspx#_decimal" rel="noreferrer">Data Type Conversion</a> and <a href="http://msdn.microsoft.com/en-us/library/ms187746%28SQL.90%29.aspx" rel="noreferrer">decimal and numeric</a>.</p> <p>Since you multiplied <code>NUMERIC(24,8)</code> and <code>NUMERIC(24,8)</code>, and SQL Server will only check the type not the content, it probably will try to save the potential 16 non-decimal digits (24 - 8) when it can't save all 48 digits of precision (max is 38). Combine two of them, you get 32 non-decimal digits, which leaves you with only 6 decimal digits (38 - 32).</p> <p>Thus the original query </p> <pre><code>SELECT A, B, C, A + B * C FROM ( SELECT CAST(0.12345678 AS NUMERIC(24,8)) AS A, CAST(0 AS NUMERIC(24,8)) AS B, CAST(500 AS NUMERIC(24,8)) AS C ) T </code></pre> <p>reduces to</p> <pre><code>SELECT A, B, C, A + D FROM ( SELECT CAST(0.12345678 AS NUMERIC(24,8)) AS A, CAST(0 AS NUMERIC(24,8)) AS B, CAST(500 AS NUMERIC(24,8)) AS C, CAST(0 AS NUMERIC(38,6)) AS D ) T </code></pre> <p>Again, between <code>NUMERIC(24,8)</code> and <code>NUMERIC(38,6)</code>, SQL Server will try to save the potential 32 digits of non-decimals, so <code>A + D</code> reduces to </p> <pre><code>SELECT CAST(0.12345678 AS NUMERIC(38,6)) </code></pre> <p>which gives you <code>0.123457</code> after rounding.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload