MS SQL Server fastest way of changing column definition

Question

Hi, I am tasked with changing a table's column datatype from smallint to int for a table that has 32 million rows in it. The requirement is to have the lowest downtime possible (basically the fastest way of achieving this). We are using SQL Server 2000.

Please help!

Answer 1

There are a couple options, you could make an online copy of the table and then use sp_rename to switch it out.

Another way is to add a new column, populate it, then drop the old column and rename the new column.

32m rows could be considered big or small depending on the width of the row.

Answer 2

Whatever you do, do not use Enterprise Manager to change the datatype. What it will do is create a whole new table, populate it, rename the old table, rename the new table to the old table's name and drop it all in one nasty transaction locking the table for hours.