Menu
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?
999
The only difference is the range of the type. INT is a 32-bit long while BIGINT is 64-bit long, therefore it can store much larger numbers like 123456789123456789 (which cannot be stored as INT ).
An identity column is an integer or bigint column whose values are automatically generated from a system-defined sequence. An identity column provides a way to automatically generate a unique numeric value for each row in a table. A table can have only one column that is defined with the identity attribute.
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.
131
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
