The Story of Precision, Scale, and Length in SQL Server
Hi guys,
Did you ever faced issues with BIG mathematical figures – related to either overflow or any other arithmetic errors. This may be applicable to SQL arithmetic operations or arithmetic operations you do with SQL table in SSIS etc.
Well, check 1 more time, if you have any issues in your database design related to column data type which stores the BIG numbers.
I ran into this issue couple of times, and reason behind this issue was same all the time. So decided to do a quick post. And that reason is “Improper or insufficient precision, scale and length defined for storing Decimals and Numeric values in table“.
I wanted to describe this with an example but there is already a very nice explanation by MSDN on this topic. Check out: http://msdn.microsoft.com/en-us/library/ms190476.aspx
You will notice in this article that, how any arithmetic operation will affect size and space allocation of the resultant value during mathematical operation. You should use following shown table for defining and designing tables in such a way that can handle BIG mathematical figures during operations.
From MSDN, for your quick view:
|
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.
Important Note: Don’t forget to check for your SQL Server Version.
I hope you will find this informative.
Thanks, Khilit
Hello! I’ve been reading your website for a long time now and finally got the courage to go ahead and give you a shout out from Atascocita Texas! Just wanted to say keep up the great work!