Sql server gives different result for the same mathematical calculation

Question

Step1

Query :

SELECT
(ISNULL(TRY_CONVERT(NUMERIC(38, 14), '123456789.34554563453524'), 0) 
 * 
ISNULL(try_convert(NUMERIC(38, 14), '456789876.34556345345353'), 0))/100.0

Result :

563938115391720.660302

Step2

The above query I have modified to :

SELECT (123456789.34554563453524 * 456789876.34556345345353) / 100.0

But the result is :

563938115391720.660302253145411988

Here, the Step1 query result has truncated the decimals at some point.

How to re-write it to get the exact number (563938115391720.660302253145411988) I got for Step2 query.

I won't mark as duplicate, but I think [this SO question](https://stackoverflow.com/questions/126401/sql-server-2005-numeric-precision-loss) might explain your observations. There are only a max of 48 digits of precision available, and your products may be exceeding this limit. Rounding error with floating point is a known problem in all programming languages. — Tim Biegeleisen, Jul 26 '17 at 13:44
Possible duplicate of [Why precision is decreasing when multiply sum to other number](https://stackoverflow.com/questions/14313598/why-precision-is-decreasing-when-multiply-sum-to-other-number) — Hybris95, Jul 26 '17 at 13:50
The chance that you actually *need* this precision is close to zero. It may actually be zero if you round it down. Note that SQL Server has no arbitrary-precision data types, so no matter that you do, there will *always* be calculations where you lose precision, even if *this one* happens to be possible. If you just want the difference explained, that's fine, but don't mislead yourself into thinking you can have exact results in all cases. — Jeroen Mostert, Jul 26 '17 at 13:50
@TimBiegeleisen The numbers are all decimal, not float. It's how SQL Server interprets literals https://learn.microsoft.com/en-us/sql/t-sql/data-types/constants-transact-sql — gbn, Jul 26 '17 at 13:51
@gbn You know more about SQL Server than I do, but I would have been shocked if the answer weren't a precision thing...this sort of question appears in the Java tag at least every few days :-) — Tim Biegeleisen, Jul 26 '17 at 13:53
This link does explain why it is truncated to 6 decimal places: https://blogs.msdn.microsoft.com/sqlprogrammability/2006/03/29/multiplication-and-division-with-numerics/ — Tab Alleman, Jul 26 '17 at 13:54
@TimBiegeleisen yes, in this case it is the precision before the decimal point the bollixes it. — gbn, Jul 26 '17 at 13:56
@TimBiegeleisen assuming that unconverted decimal values like "100.1" are implicitly converted to float by SQL Server, I tried a variation of OP's first query where I converted 100.0 to numeric, and it yielded the same result. I even removed the ISNULL and the division by 100.0, and it still truncated the decimal, so I considered this proof that float-precision was not a factor. — Tab Alleman, Jul 26 '17 at 13:56
@TabAlleman the numbers are all decimal in SQL Server. No "E" https://learn.microsoft.com/en-us/sql/t-sql/data-types/constants-transact-sql — gbn, Jul 26 '17 at 13:58

gbn · Accepted Answer · 2017-07-26T13:55:21.447

This is exactly as predicted by decimal precision. See T-SQL Decimal Division Accuracy

Numbers with decimal points are interpreted as NUMERIC in SQL Server to the exact precision shown. Not float. In this case decimal (23, 14)

In the first example they are decimal (38, 14)

Same result when using the same precision with explicit CONVERTs to the same datatype

SELECT
(ISNULL(TRY_CONVERT(NUMERIC(23, 14), '123456789.34554563453524'), 0) 
 * 
ISNULL(try_convert(NUMERIC(23, 14), '456789876.34556345345353'), 0))/100.0

563938115391720.660302253145411988

Note, the 100.0 is decimal(4,1) in this case too

Final words: this is long division that used to be taught at school after some precision changes because of the 38/23 difference in the multiplication

Sql server gives different result for the same mathematical calculation

1 Answers1