Changing INT to BigInt

问题:

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?

回答1:

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.

回答2:

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

回答3:

This technique worked really nicely for me.

I executed:

use [Mytable]
ALTER TABLE [dbo].[USER] ALTER COLUMN USER_ID bigint NOT NULL

This resulted in this error because there was a constraint on the key:

Msg 5074, Level 16, State 1, Line 2
The object 'PK_USER_USER_ID' is dependent on column 'USER_ID'.
Msg 4922, Level 16, State 9, Line 2
ALTER TABLE ALTER COLUMN USER_ID failed because one or more objects access this column.

Not to be deterred, in SQL Server Management Studio I right clicked on the constraint PK_USER_USER_ID, then selected "Script key as >> Drop and Create To >> New Query Editor Window":

This generated this script:

USE [Database]
GO
/****** Object:  Index [PK_USER_USER_ID]    Script Date: 18/03/2014 13:05:38 ******/
ALTER TABLE [dbo].[USER] DROP CONSTRAINT [PK_USER_USER_ID]
GO

/****** Object:  Index [PK_USER_USER_ID]    Script Date: 18/03/2014 13:05:38 ******/
ALTER TABLE [dbo].[USER] ADD  CONSTRAINT [PK_USER_USER_ID] PRIMARY KEY CLUSTERED 
(
[USER_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,        ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

I then executed the first half of this script, to drop the constraint:

ALTER TABLE [dbo].[USER] DROP CONSTRAINT [PK_USER_USER_ID]
GO

Now that the constraint was gone, the original change worked nicely:

use [Mytable]
ALTER TABLE [dbo].[USER] ALTER COLUMN USER_ID bigint NOT NULL

I then executed the second half of the script, to add the constraint back in:

ALTER TABLE [dbo].[USER] ADD  CONSTRAINT [PK_USER_USER_ID] PRIMARY KEY CLUSTERED 
(
[USER_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,        ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

回答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.

回答5:

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.

回答6:

If the source data never goes over INT limit, just make a VIEW that upcasts that column to BIGINT and query against that.