SQL Server performance for alter table alter column change data type

performance, sql, sql-server

Solution

Coincidentally, I had to do something very similar about 3 hours ago. The table was 35m rows, it is fairly wide, and it was taking forever to just do this:

alter table myTable add myNewColumn int not null default 0;

Here's what what I ended up going with:

alter table myTable add myNewColumn int null;

while 1=1
begin
    update top (100000) myTable
    set
        myNewColumn = 0
    where
        myNewColumn is null;

    if @@ROWCOUNT = 0 break;
end

alter table myTable alter column myNewColumn int not null;
alter table myTable add constraint tw_def_myNewColumn default (0) for myNewColumn;

This time around, the `alter table` statements were near-instant. It took about 7-8 minutes (on a slow server) to do the update batches. I'm speculating that SQL Server was generating undo in my original query to restore the values, but I didn't expect that starting off.

Anyway, in your case, maybe something similar would help. You could try adding a new bigint column, update the new column in batches, then set the constraints on it.

Problem

We need to change the data types of some columns from int to bigint. Unfortunately some of these tables are large, around 7-10 million rows (but not wide). Alter table alter column is taking forever on these tables. Is there a faster way to achieve this?

Original source