Alter the precision of an existing decimal column in SQL Server 2014 results in error

Question

I have a couple decimal columns in a couple tables that I need to expand from decimal(9, 4) to decimal(9, 5). Unfortunately I get the following error every way I try. So far I have tried changing it using design in SQL Server Management Studio and running the query:

alter table xxx 
alter column xxx decimal (9,5) not null

The error is the same:

Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.

These are large, important tables that are used in dozens of stored procedures and applications so I want to avoid adding a new column and copying the data over. Each table has between 250K and 500K rows.

Any ideas would be appreciated. Thanks!

Answer 1

This is because you have reduced the number of significant digits when you changed the datatype. If you have any rows with a value of 100,000 or greater it won't fit inside the new size. If you want to increase the number of decimal places (scale) you will also need to increase the precision by 1.

alter table xxx alter COLUMN xxx decimal (10,5) not null

Please note, this will increase the storage requirements of each row by 4 bytes. The storage requirement will increase from 5 bytes to 9 bytes.

https://msdn.microsoft.com/en-us/library/ms187746.aspx