Changing INT to BigInt

Question

I have a warehouse table with 16 tons of data in it. I have a few Integer columns in it. We have to cast these into BIGINT for every query we write, because the SUM is too large to fit in an INT.

We now have a new datamart under development. So we thought, why not change all these columns into BIGINT and we have less to worry for the new set of queries.

Since the data is already loaded, I figured I would use Management Studio and change the data type. But I first get a warning:

Saving Definition Changes to tables with large amounts of data could take a considerable amount of time. While changes are being saved, table data will not be accessible.

Then I get an error:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

How do I get around this?

Answer 1

Not sure if this will help, but try this:

1 - create a new bigint column in the table
2 - update that new column with the values from the int column
3 - delete the int column
4 - rename the bigint column

Answer 2

to expand on OrbMan's answer:

• add the new columns at the bottom of the column list (this will speed it up a lot)
• you can do your updates in batches of 10,000 rows or so if necessary
• make sure you are in single user mode, or the application if "OFF" so no one else changes data in that table

Also, to see all the work that Management studio does when you change a table, click on the toolbar icon that looks like scroll with a diskette on it. This will show the actual SQL commands used to alter your table.

Answer 3

If one or more of those columns have no constraints against them (like a foreign key, index, default, rule, etc), you should be able to change each one quickly by doing

ALTER TABLE monster ALTER COLUMN MyIntCol1 bigint

Management Studio's change SQL is rarely the most efficient and tends to favour temporary tables for anything modifying an existing column.

Answer 4

I think the main error you maybe running into is that the GUI is what is timing out. When you apply a big change using the Modify selection from SSMS it will timeout. If you take the same command by generating the change script in SSMS and then run as a straight SQL query it will run until completed.