I learned something new about decimal data types in SQL Server recently. If you do something like this:
DECLARE @d DECIMAL(10,10) SET @d = 1.0
You get this:
Msg 8115, Level 16, State 8, Line 2
Arithmetic overflow error converting numeric to data type numeric.
That threw me for a minute since I’d never experienced it, but then I realized what was going on. The declaration of a decimal data type goes like this:
DECLARE @d DECIMAL( @p, @s)
Where we have
- d – the name of the variable
- p = precision, the number of digits in the number.
- s = scale, the number of digits for the decimal
You can think of this as the following,
@d = nnnn.ssss
Where the count of n’s and s’s must equal p.
In the first example above, I’ve essentially declared:
@d = 0.ssssssssss
There’s no room for a digit, other than zero, to the left of the decimal. So you get an error. If I’d added one more digit to the p variable, like this:
DECLARE @d DECIMAL(11,10) SET @d = 1.0
I don’t receive an error. Likewise, I can add multiple digits to the other side like this:
DECLARE @d DECIMAL(10,10) SET @d = 0.999999999
I had never run into this because I don’t ever declare these the same. I almost always go larger than I need, and allow for more decimals. So for US money, I often declare things at decimal(10,3), giving me more space than needed. It pays to think ahead, and declare your variables properly, and understanding how they are structured is part of that.
Note: this applies to numeric types as well.