Why doesn't `1` fit into a decimal(4, 4) column?

Question

I have a decimal(4, 4) column in MS SQL Server 2008 R2. If I understand correctly, that means:

• Precision of 4, ie up to four digits can be stored after the decimal place
• Scale of 4, ie a a total of four digits can be stored

When I run an update command to set the column to 1 (update myTable set myDecimalColumn=1), I get this error:

Arithmetic overflow error converting numeric to data type numeric.

I don't understand how that is true. 1 has no digits after the decimal and is only one digit long.

Answer 1

The Precision specifies the total number of digits that can be stored, the Scale specifies, how many of those digits live behind the decimal point.

A DECIMAL(4,4) therefore has four digits and all are behind the decimal point. That means you can store values form -0.9999 to 0.9999. You cannot store -1 or 1 as they have a digit before the decimal point.

SQL Fiddle

Query 1:

SELECT CAST(-0.9999 as DECIMAL(4,4)),CAST(0.123456 as DECIMAL(4,4)),CAST(0.9999 as DECIMAL(4,4))

Results:

| COLUMN_0 | COLUMN_1 | COLUMN_2 |
----------------------------------
|  -0.9999 |   0.1235 |   0.9999 |

To store 1 you have to add an additional digit as in DECIMAL(5,4). That allows you to store values from -9.9999 to 9.9999:

SQL Fiddle

Query 2:

SELECT CAST(-9.9999 as DECIMAL(5,4)),CAST(1 as DECIMAL(5,4)),CAST(9.9999 as DECIMAL(5,4))

Results:

| COLUMN_0 | COLUMN_1 | COLUMN_2 |
----------------------------------
|  -9.9999 |        1 |   9.9999 |