CODEWITHSUNDEEP
sql-serverdecimal
harshil
harshil

Reputation: 11

SQL Server decimal division not rounding the result

I tried the below multiplication and division and expected same result with rounding. Both operations has result type as Decimal(38,6) I want to know why result is not rounded in case of division?

result should be rounded for both multiplication and division as per below link enter link description here

select cast(1.225114854 as decimal(38, 9)) * cast(1.000000000 as decimal(28, 9)) as multiply

Result : 1.225115

select cast(1.225114854 as decimal(38, 9)) / cast(1.000000000 as decimal(28, 9)) as division

Result: 1.225114

select 1.225114854 / 1.000000000

Result: 1.22511485400000000000

Upvotes: 1

Views: 337

Answers (2)

wrschneider
wrschneider

Reputation: 18810

I had same issue with converting SQL Server to Spark.

Both SQL Server and Spark use the same rules for determining precision and scale for results of arithmetic operations, listed in @CR7SMS's answer.

I believe the problem is the SQL Server documentation link you shared is incorrect, because it uses the word "round" specifically: "Result might be rounded to 6 decimal places". But division truncates rather than rounds.

I resolved this in Spark by using a UDF for division that replicates the behavior in SQL Server:

val divide = udf((x: BigDecimal, y: BigDecimal) => x.divide(y, 6, RoundingMode.FLOOR))

Upvotes: 0

CR7SMS
CR7SMS

Reputation: 2584

The way SQL server behaves in case of multiplication and division of decimal numbers is different, hence the difference in number of digits:

Assuming, e1=decimal(p1,s1) and e2=decimal(p2,s2)

Operation   Result precision                        Result scale *
e1 + e2     max(s1, s2) + max(p1-s1, p2-s2) + 1     max(s1, s2)
e1 - e2     max(s1, s2) + max(p1-s1, p2-s2) + 1     max(s1, s2)
e1 * e2     p1 + p2 + 1                             s1 + s2
e1 / e2     p1 - s1 + s2 + max(6, s1 + p2 + 1)      max(6, s1 + p2 + 1)

The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated.

Because of this, when you are multiplying 38 precision decimals, the result becomes truncated. You can use the following calculation to get better results:

select cast(1.225114854 as decimal(15,9))*cast(1.000000000  as decimal(15,9)) as multiply
select cast(1.225114854 as decimal(15,9))/cast(1.000000000  as decimal(15,9)) as division

The following link should be able to clarify most of your doubts:

https://www.codeproject.com/Articles/769823/Losing-Precision-After-Multiplication-in-SQL-Serve

Upvotes: 2

Related Questions