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:

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:


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

5 (100%) 5 votes

One Comment

Leave a Reply