3

When executing the following query in SQL Server:

SELECT 
    CAST(22) AS decimal(38, 10) / CAST(7) AS decimal(38, 10) AS pie

the output is: 3.142857.

But when I run the same query with lower precision of 20 I get more values after the decimal

SELECT
    CAST(22) AS decimal(20, 10) / CAST(7) AS decimal(20, 10) AS pie

The output is : 3.142857142857142857

Can anyone explain why this is so?

I have a similar situation in SQL Server where I need the accurate value up to 10 decimal places when I divide two decimal(38, 10) columns.

Problem being, these two columns are coming from a separate table, where they are defined as DECIMAL(38, 10).

i4nk1t
  • 419
  • 1
  • 6
  • 16
  • Does this answer your question? [T-SQL Decimal Division Accuracy](https://stackoverflow.com/questions/423925/t-sql-decimal-division-accuracy) – GSerg Mar 27 '21 at 21:53

1 Answers1

2

There are only 38 digits of storage, so scale can be lost to preserve high-order digits in calculations.

This is all explained in the docs:

enter image description here

Precision, scale, and Length (Transact-SQL)

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • So is there a way i can cast the two columns down to 20,10 just for the computation purpose and to achieve accuracy of 10 decimal places ? – i4nk1t Mar 27 '21 at 21:32
  • Yes. Cast them to a lower-precision decimal or to float for the calculation. Or better yet, reduce the declared precision of the columns. – David Browne - Microsoft Mar 27 '21 at 21:34
  • Declared precision cannot be reduced as the columns were defined by the DBA , i can just create a Table Valued Function or View over it. Casting them to a lower precision gives an error saying trying to cast numeric to data type numeric. And float just returns the value till 6 decimals – i4nk1t Mar 27 '21 at 21:41