Click here to Skip to main content
Click here to Skip to main content
Go to top

Losing Precision After Multiplication in SQL Server

, 7 May 2014
Rate this:
Please Sign up or sign in to vote.
Losing precision after multiplication in SQL Server

Yesterday, I was doing a little multiplication operation in SQL SERVER. The computation involves multiplication of two decimal numbers. A point to note here is that I've to be very careful while performing the calculations because it's a monetary thing. So, whatever the calculation made, it must be very accurate such that I shouldn't miss any fractions as it costs in MILLIONS.

The scenario is that I've two numbers, after multiplying each other I'm losing the precision of the values. I've tried to check my database for any faulty data that caused the issue. But the data is absolutely fine.

Here is what the SQL looks like:

DECLARE @NUM1 DECIMAL(32, 10)  
DECLARE @NUM2 DECIMAL(32, 10)  
SET @NUM1 = 1.86975178  
SET @NUM2 = 6.65781856     
SELECT @NUM1 * @NUM2  
--The expected output: 12.4484681034770368000  
--The original output: 12.448468

I was surprised to see the result that I've got only has 6 decimal places. What went wrong?

To understand the problem, we need to have an idea of the concept called Precision, Scale and Length. The following table depicts that there are few rules to be followed when we are calculating the numeric values or decimal values.

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, the corresponding scale is reduced to prevent the integral part of a result from being truncated.

For those who don't know what precision and scale is, precision is the total number of digits and scale is the number of decimals. Say for example, a data type decimal(10,5). Then the precision is 10 and scale is 5. So for multiplication of two decimal numbers, the result precision and the resultant scale are shown as follows:

Precision = P1 + P2 +1 = 32 + 32 + 1 = 65
Scale = S1 + S2 = 10 + 10 = 20

Theoretically, the result should be of DECIMAL(65,20), but this is INVALID as the maximum capacity of the DECIMAL is 38 only. As the * below the table states, if the result is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated. So our result precision is greater than 38, SQL server tries to avoid truncating the integral part by reducing the scale, i.e., instead of removing the integer part, SQL server will truncate the decimal part. SQL SERVER team has decided to preserve scale of 6 in both division and multiplication scenarios. So our DECIMAL(65,20) is turned into DECIMAL(38,6).

The solution is that we need to minimize the use of precision and scale in the data type that we use such that the resultant precision is not exceeding 38. Now in this case, we'll adjust the datatype to get some accuracy in the data.

DECLARE @NUM1 DECIMAL(18, 10)   
DECLARE @NUM2 DECIMAL(18, 10)       
SET @NUM1 = 1.86975178   
SET @NUM2 = 6.65781856       
SELECT @NUM1 * @NUM2   
--Our result: 12.44846810347703680000

Now the resultant data type will be DECIMAL(37,20) which is less than the maximum of the decimal's precision limit and hence no implicit reduction is performed.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Karthik Chintala
Software Developer (Junior)
India India
A programmer.
 
Follow my Blog
Follow on   Twitter

Comments and Discussions

 
GeneralMy vote of 5 Pinmembereghetto27-May-14 21:08 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140905.1 | Last Updated 7 May 2014
Article Copyright 2014 by Karthik Chintala
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid