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

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.

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 |

StackExchange Database Administrators Q#47948, answer score: 7