CODEWITHSUNDEEP
sqlsql-server
user1907849
user1907849

Reputation: 980

SQL Server Numeric with different precision

What is the difference between numeric(30,10) and numeric(20,10) in sql server and why is that 3.456/2.8 gives result as 1.2342857100 in first case and 1.2342857143 in second case?

Upvotes: 0

Views: 1520

Answers (3)

Thom A
Thom A

Reputation: 95554

numeric(30,10) is a numeric with a precision of 30, and a scale of 10, where as the latter has a precision of 20 and a scale of 10. For the former, this means the value can contain up to 30 digits, with 10 of those after the decimal point. The latter up to 20 digits, with 10 of those after the decimal point.

As for why they give different values, that's all explained in Precision, scale, and Length (Transact-SQL), where the resulting scale and precision is calculated as below:

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)
e1 { UNION / EXCEPT / INTERSECT } e2 max(s1, s2) + max(p1-s1, p2-s2) max(s1, s2)
e1 % e2 min(p1-s1, p2 -s2) + max( s1,s2 ) max(s1, s2)

* The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, it's reduced to 38, and the corresponding scale is reduced to try to prevent truncating the integral part of a result. In some cases such as multiplication or division, scale factor won't be reduced, to maintain decimal

Upvotes: 2

Amira Bedhiafi
Amira Bedhiafi

Reputation: 1

Suppose that you defined a column as numeric(30,10) which means a precision p of 30 and a scale s of 10.

This is the logic behind : p1 - s1 + s2 + max(6, s1 + p2 + 1)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269553

SQL Server has very detailed rules on how scale and precision are affected by arithmetic operators, documented here. For addition and subtraction, it is pretty simple.

For division, the logic is . . . rather more convoluted.

The reason is because the precision is set by this formula: p1 - s1 + s2 + max(6, s1 + p2 + 1). To be honest, I don't know how they came across this formula, but it is why the precision is what it is.

Upvotes: 1

Related Questions