SharePoint Newbie SharePoint Newbie - 1 year ago 149
SQL Question

SQL Server performance for alter table alter column change data type

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?

Answer Source

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
    update top (100000) myTable
        myNewColumn = 0
        myNewColumn is null;

    if @@ROWCOUNT = 0 break;

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download