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?
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.