Increasing the decimal precision abruptly increased the size of table

I have a table like:

CREATE TABLE grid_rows(
    [grid_row_id] [int] NOT NULL,
    [grid_column_id] [smallint] NOT NULL,
    [decimal_val] [decimal](18, 6) NULL,
    [datetime_val] [datetime] NULL,
    [integer_val] [int] NULL,
    [string_val] [varchar](1024) NULL
)

This table has some 1,037,560 rows

exec sp_spaceused "grid_rows" gives:
rows         reserved    data
1,037,560    461,768KB  302,648KB`

After changing the precision from (18, 6) to (24,6) i.e
ALTER TABLE grid_rows ALTER COLUMN decimal_val decimal(24, 6)

exec sp_spaceused "grid_rows" gives:
rows         reserved     data
1,037,560    641,352KB  560,832KB

The space allocated by decimal(18,6) is 9 bytes and that of (24, 6) is 13 bytes. MSDN reference

The reserved space has increased by around 179,584 KB and data space by 260,000KB. Shouldn't it be increased by 1,037,560 * 4/1024 = 4052 KB

Keep in mind that the dropped column is still physically present in the row.

But the real killer is probably that, if this is a heap (table w/o clustered index), then row updates that increase the row size may cause an avalanche of forwarding. Consider that if you start with pages at nearly 100% full then any row increase will cause the row to no longer fit in the page, so a forward record has to be left in place and the row has to be placed somewhere else. Repeat this for every row (extreme case) and you have a pretty nasty scenario.

If is a B-Tree then the same scenario will cause page splits which are better because during the split the copied rows reclaim the space between the rows on the page, which the heap forwarding case does not.

StackExchange Database Administrators Q#39828, answer score: 4