SQL 将 INT 更改为 BigInt
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1462337/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Changing INT to BigInt
提问by Raj More
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.
我有一个仓库表，里面有 16 吨数据。我有几个整数列。我们必须为我们编写的每个查询将这些转换为 BIGINT，因为 SUM 太大而无法放入 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.
我们现在正在开发一个新的数据集市。所以我们想，为什么不把所有这些列都改成 BIGINT，这样我们就不用担心新的查询集了。
Since the data is already loaded, I figured I would use Management Studio and change the data type. But I first get a warning:
由于数据已经加载，我想我会使用 Management Studio 并更改数据类型。但我首先收到警告：
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.
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.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
How do I get around this?
我该如何解决这个问题？
回答by MartW
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
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.
Management Studio 的更改 SQL 很少是最有效的，并且倾向于支持临时表来修改现有列。
回答by RedFilter
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
回答by Contango
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 StudioI right clicked on the constraint PK_USER_USER_ID, then selected "Script key as >> Drop and Create To >> New Query Editor Window":
不要被吓倒，在SQL Server Management Studio 中，我右键单击约束 PK_USER_USER_ID，然后选择“脚本键为 >> 删除并创建到 >> 新建查询编辑器窗口”：
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 halfof 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 halfof 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
回答by Chris Coneybeer
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.
我认为您可能遇到的主要错误是 GUI 超时。当您使用 SSMS 中的“修改”选项应用重大更改时，它将超时。如果您通过在 SSMS 中生成更改脚本来执行相同的命令，然后作为直接 SQL 查询运行，它将一直运行直到完成。
回答by Hafthor
If the source data never goes over INT limit, just make a VIEW that upcasts that column to BIGINT and query against that.
如果源数据永远不会超过 INT 限制，只需创建一个 VIEW 将该列向上转换为 BIGINT 并对其进行查询。
回答by KM.
to expand on OrbMan'sanswer:
扩展OrbMan 的回答：
- 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
- 在列列表的底部添加新列（这会加快很多速度）
- 如有必要，您可以批量更新 10,000 行左右
- 确保您处于单用户模式，或者应用程序如果“关闭”，则没有其他人更改该表中的数据
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.
此外，要查看管理工作室在更改表时所做的所有工作，请单击工具栏图标，该图标看起来像滚动条，上面有一张软盘。这将显示用于更改表的实际 SQL 命令。
