The table has over 2 billion rows and it looks like it’s going to run out of space soon because the
LogIdcolumn is defined as an
INT. I need to change this table so that it’s a
BIGINT. But changing an
BIGINTis known as a “size of data” operation. This means SQL Server has to process every row to expand the LogId column from 4 to 8 bytes. But it gets trickier than that.
The biggest challenge is that the table has to remain “online” (available for queries and inserts).
Gianluca Sartori (spaghettidba) had the idea of enlarging the columns with no downtime using compression. It’s promising, but I discovered that for this to work, all indexes need to be compressed not just the ones that contain the changed column. Also, any indexes which use the column need to be disabled for this to work.
I gave up on solving this problem in general and constrained my focus to the specific problem I was facing. There’s always some context that lets us bend the rules. In my case, here’s what I did.
Read the whole thing. Also, as soon as I saw Michael’s title, I immediately thought of this: