ALTER TABLE ALTER COLUMN failed for table 'Amount' because it is a ledger table and the operation would need to modify existing data that is immutable.

Question

I have an updatable ledger table having a column with the datatype DECIMAL(38,4), I want to alter that column and change the datatype to DECIMAL(38,8). But I am getting this error.
ALTER TABLE ALTER COLUMN failed for table 'Amount' because it is a ledger table and the operation would need to modify existing data that is immutable.

Answer 1

would need to modify existing data that is immutable.

And that would do your planned ALTER command, your risk loosing data and this in a ledger table; worst case ever.

datatype DECIMAL(38,4), I want to alter that column and change the datatype to DECIMAL(38,8)

38 is the total sice for the data type, and 4/8 a the size for decimals.

Or in other words, you are going to change 34-4 to 30-8

If you want to change the data type, add an additional columns in planned size, copy over the data and validate everything on data loss; then you can rename the columns.

Answer 2

Simple test; it fails = data gone.

DECLARE @test decimal(10, 4) = 123456.1234

SELECT @test, try_convert(decimal(10, 8), @test)

Answer 3

Hi @Vijay Pratap Singh

See this tech doc:

Any operations that might affect the format of existing data, such as changing the data type aren't supported.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".