[Video] Altering Datatypes With (Almost) No Downtime


Sometimes, your INT columns run out of space, and you need to alter them to be a larger datatype, like BIGINT. Usually, when you do this, you’re met with screams of agony from your end users because this will totally lock the table and rewrite the whole thing:

ALTER TABLE dbo.Posts ALTER COLUMN OwnerUserId BIGINT;

In SQL Server 2016 & newer, you can even kinda-sorta do it online with this syntax – but only if there are no indexes referring to the column:

ALTER TABLE dbo.Posts ALTER COLUMN OwnerUserId BIGINT WITH (ONLINE = ON);

However, Gianluca Sartori (@SpaghettiDBA on Twitter) wrote about