Tuesday, December 22, 2015

Numeric scale and precision

From the quick tip file:

I have lost track of the number of times I have seen people get this wrong. The precision of numeric doesn't specify the number of digits allowed before the decimal point, it specifies the total number of digits before and after the decimal point. So the number allowed before the decimal point is the precision minus the scale. And that means that if you want to increase the scale for finer grained numbers without reducing the range of numbers allowed you need to increase both the precision and the scale.

Let's say you want to have money amounts of US dollars with no fractional cents and up to single trillions of dollars. You would use numeric(15,2). That allows thirteen digits before the decimal and two after. Now if you want to change that to allow fractional cents in hundredths, you will need to use numeric(17,4), which still allows thirteen digits before the decimal but now allows four after.

No comments:

Post a Comment