bigator

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

bigatorJanuary 28, 2013 · bigatorKhilitchandra Prajapati · bigatorOne Comment
bigatorTags: , , , , ,  · Posted in: Business Intelligence, Free, General, Interview QA, Quick Fix, Quick Tips, Script, SQL Server, SQL Server 2012, SQLServerPedia, SSAS, SSIS, SSRS, T-SQL, Tutorial

One Response

  1. medical school application consultant - August 2, 2013

    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!

Leave a Reply

Current day month ye@r *


%d bloggers like this:
bigator