SQL Server 2008 – Alter Table Column Performance Optimization

sql-server-2008

We have a table with more than 10million rows.

We are altering its column type like this:

ALTER TABLE MY_TABLE ALTER COLUMN MY_COLUMN VARCHAR(3)

What would be the fastest way to do this? Alter column needs to touch EVERY row right? Is there a way to make this faster?

According to questions and answers here, I'm thinking about, create a new table and migrate data, or create a new column with VARCHAR(3) and migrate only the data inside this column and drop the old column.

Obs. It's taking more than 3hrs.

Best Answer

Referencing Aaron Bertrand's answer to a similar question, he says:

Depending on what change you are making, sometimes it can be easier to take a maintenance window. During that window (where nobody should be able to change the data in the table) you can:

1). drop any indexes/constraints pointing to the old column, and disable triggers

2). add a new nullable column with the new data type (even if it is meant to be NOT NULL)

3). update the new column setting it equal to the old column's value (and you can do this in chunks of individual transactions (say, affecting 10000 rows at a time using UPDATE TOP (10000) ... SET newcol = oldcol WHERE newcol IS NULL) and with CHECKPOINT to avoid overrunning your log)

4). once the updates are all done, drop the old column

5). rename the new column (and add a NOT NULL constraint if appropriate)

6). rebuild indexes and update statistics

The key here is that it allows you to perform the update incrementally in step 3, which you can't do in a single ALTER TABLE command.

This assumes the column is not playing a major role in data integrity - if it is involved in a bunch of foreign key relationships, there are more steps.

Related Question