How to decide the precision and scale of a decimal column – Part 3

In this third and final part of the series, I am discussing about the arithmatic operations and rounding (though briefly) on decimals.

When doing arithmetic operations on decimal numbers, you should be careful since the resulting decimal number will have different precision and scale.

For example, execute the below script

DECLARE @D1 AS DECIMAL (3, 1) = 99.9
DECLARE @D2 AS DECIMAL (3, 1) = 99.9
SELECT @D1 + @D2, CAST(@D1 + @D2 AS VARBINARY)

Image

The addition of two decimal numbers having a precision 3 resulted in a decimal number having precision 4 as evident from the hexadecimal conversion of the result. The precision and scale will change dramatically, if the operation involved is multiplication or division as below.

DECLARE @D1 AS DECIMAL (3, 1) = 99.9
DECLARE @D2 AS DECIMAL (3, 1) = 99.9

SELECT @D1 * @D2 AS MULTIPLICATION, CAST(@D1 * @D2 AS VARBINARY)
SELECT @D1 / @D2 AS DIVISION, CAST(@D1 / @D2 AS VARBINARY)

Image

Try changing the precision and scale of @D1, and @D2 variables (without changing the assigned value), and check the resultant precision and scale.

So far, you don’t have any issue.  Suppose ARITHABORT or ANSI_WARNINGS settings are ON. In that case if you try to assign this result to a variable with precision 3, you will get arithmetic overflow error.

DECLARE @D1 AS DECIMAL (3, 1) = 99.9
DECLARE @D2 AS DECIMAL (3, 1) = 99.9
DECLARE @D3 AS DECIMAL (3, 1)

SELECT @D1 + @D2, CAST(@D1 + @D2 AS VARBINARY)
— This will cause Arithmetic overflow error, If
— SET ANSI_WARNINGS ON, — or
— SET SET ARITHABORT ON
SELECT @D3 = @D1 + @D2

How to avoid this overflow error? First method is just set the above settings OFF. However this is not recommended.

The second method is assigning the result to a variable with properly defined precision and scale and use explicit cast. This is the recommended method. For example,

DECLARE @D1 AS DECIMAL (3, 1) = 99.9
DECLARE @D2 AS DECIMAL (3, 1) = 99.9
DECLARE @AddResult AS DECIMAL (4, 1)
DECLARE @MulResult AS DECIMAL (7, 2)

SELECT
@AddResult = CAST (@D1 + @D2 AS DECIMAL (4, 1))
,@MulResult = CAST (@D1 * @D2 AS DECIMAL (7, 2))
SELECT @AddResult AS ‘ADD’, @MulResult AS ‘MUL’

So how do you decide the precision and scale of the result for each arithmetic operation? Microsoft provides the below formula in Books Online:

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 is expression1 and e2 is expression2; p1 and s1 are precision and scale of e1 respectively, and p2 and s2 are precision and scale of e2 respectively. Using these formulas we can easily calculate the resulting precision and scale.

One more example let us say a product has a unit price value stored as DECMAL (9, 4) and a fractional quantity purchased as DECIMAL (5, 2). What will be the precision and scale to store the total amount (i.e. total amount = unit price X quantity). Apply the formula from the above table for e1 * e2; the result precision will be 9 + 5 + 1 = 15, and scale will be 6. However if you need the total amount in four decimal places you need to round this accordingly. Let us verify this.

DECLARE @UnitPrice AS DECIMAL (9, 4) = 452.1250
DECLARE @Quantity AS DECIMAL (5, 2) = 3.15
DECLARE @TotalAmount AS DECIMAL (15, 4)

SELECT @TotalAmount = CAST(@UnitPrice * @Quantity AS DECIMAL (15, 4))

Please note that these operations require rounding of decimal numbers without losing the precision. SQL Server will do this rounding internally based on the algorithms mentioned in Part 1.

Conclusion

In this series (Part 1, Part 2 & Part 3), I have explained how a decimal number is represented and how arithmetic operations are impacting the precision and scale of the resultant decimal number. I hope, a clear understanding on these details will help you to design better and error-free codes.

Advertisements

Leave a comment

Filed under Basics

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s