将标识列从int更改为bigint

Question

我正在使用SQL Server 2008，有一个包含约五千万行数据的表。

该表包含一个名为identity的主键列，类型为int。

我希望将该列升级为bigint类型。

我需要知道如何快速实现此操作，不会使我的数据库服务器不可用， 也不会删除或破坏任何数据。

我应该如何最好地执行此操作？这样做会有什么后果？

- Matan L

---

不仅是这个表本身，每个具有FK关系到该列的表都需要被锁定。您只使用了MAXINT的约2.5％; 这是一个紧迫的问题还是只是“做好准备”？ - Pieter Geerkens

为什么？！？！一个类型为“INT”的列可以提供2亿行数据！这比你现在使用的数量多40倍以上。你在多快的速度使用这些数字？很可能，这个“INT”列在很长一段时间内仍远远足够你的系统使用！ - marc_s

正在准备接下来几周要做的事情...我必须去做...那么，最好的方法是什么？ - Matan L

Answer 1

嗯，这并不是一种快速简单的方法...

我的做法是:

1. 创建一个结构相同但 ID 列为 BIGINT IDENTITY 而不是 INT IDENTITY 的新表

   ----[ 将你的服务器置于独占单用户模式; 此时用户无法使用你的服务器 ]----

2. 找到并禁用所有引用原表的外键约束

3. 打开 SET IDENTITY_INSERT (your new table) ON

4. 将旧表中的行插入到新表中

5. 关闭 SET IDENTITY_INSERT (your new table) OFF

6. 删除旧表

7. 将新表重命名为旧表名

8. 将所有引用原表的表更新为使用 BIGINT 而不是 INT (可以通过简单的 ALTER TABLE ..... ALTER COLUMN FKID BIGINT 完成)

9. 重新创建所有外键关系

10. 现在你可以将服务器返回到正常的多用户使用状态了

- marc_s

---

注意：在步骤2到9期间，要么使服务器不可用，要么由于缺少FK查找和验证，可能会使所有数据修改在转换期间无效。 - Pieter Geerkens

如果我停止访问数据库，进入表格=>编辑，将列类型更改为bigint并保存，会发生什么？ - Matan L

@MatanL：然后SQL Server Mgmt Studio中的可视化设计器将尝试创建新表，复制数据，删除旧表，将新表重命名为旧表的名称 - 但如果您的表上有任何FK引用，它将失败。正如我在回答开始时所说的那样：这不是您想要进行的快速且容易的更改！所以再次问：为什么您甚至考虑这个呢？INT对于您的数据库已经足够了！ - marc_s

我正在开发一个项目，很快就要处理这些数字...问题是我在整个项目中使用了行的身份标识，有些行偶尔会被删除...因此重新插入所有数据到新表将导致我的信息不同... - Matan L

如果你使用SET IDENTITY_INSERT ON，那么你将会插入完全相同的ID值到你的新表中！ - marc_s

Answer 2

我错过了什么？

为什么你不能就这样做：

ALTER TABLE tableName ALTER COLUMN ID bigint

我想首先在测试环境下尝试一下，但这种方法对我总是有效。

- MobileMon

---

我刚试了一下，你可以这样做。谢谢你发布这个帖子，因为在阅读了上面的答案后，我本来不打算尝试它。它完全正常工作，并保留了标识列的种子。 - JohnOpincar

今天我在创建新表时，突然想到了这个问题。我认为只需要一个ALTER语句就可以了。但是搜索后发现答案并不那么简单。我猜想如果表中没有外键引用和约束，这个语句应该可以正常工作，否则会失败。我可能会在测试数据库上尝试一下，并发布结果。 - Ankur-m

啊，有趣，从来没想过外键的情况。请告诉我！ - MobileMon

你所忽略的要点是：1）如果表格有大量记录，这个查询将会运行很长时间，同时增加mdf/ldf文件的大小；2）如何处理外键（FK）方案。 - willsteel

@willsteel 在外键的情况下，你可以：1）删除它们。2）更改主表中的列。3）将从属表中的列（具有FK的表）更改为BIGINT。4）重新创建FK（例如https://stackoverflow.com/a/37002330/4850646，它是关于MySQL的，但应该以相同的方式运行）。关于一个拥有太多行的表，我认为当一个表达到100万行时，应该考虑这样的变化，以避免后来的不便，否则即使接受的答案也可能无法避免停机时间。在此期间，您可以使您的数据库处于只读状态，以避免完全停机。 - Lucas Basquerotto

Answer 3

最好的方法可能是创建一个带有BIGINT IDENTITY列的新表，使用SET IDENTITY_INSERT ON移动现有数据，然后重命名表格。您需要在维护窗口期间执行此操作，就像在Management Studio中更改数据类型时一样（这也会创建一个新表格，移动数据并阻止所有人进行操作）。

- Aaron Bertrand

Answer 4

你可以像 @MobileMon 所说的那样使用 Alter 脚本来更改列，但是在删除约束之前不能这样做。除了 FK 约束之外，在更改列类型之前还必须删除 PK 约束！

另外还有另一种创造性的方法，如果 ID 数据不重要（没有 FK 等）：

1. 备份表格（如果它在单独的文件组中）或数据库
2. 重命名表格（没有插入操作）
3. 从列中删除 PK/约束
4. 删除 ID 列
5. 添加新的 ID 列，并启用 Identity
6. 应用 PK
7. 将表格重命名回原始名称（回到工作状态：)

& 如果 ID 数据很重要：

1. 执行与上面相同的步骤 1,2
2. 创建一个新列
3. 将现有的 IDENTITY 列中的数据转移到新列中
4. 删除现有的 IDENTITY 列和 PK。
5. 创建新的列，启用 Identity
6. 应用 PK
7. 将表格重命名回原始名称（回到工作状态：)

重要提示：1. 如果旧列ID值不重要且值之间有很大的间隔（除了插入之外还有删除），则不需要使用BigInt。只需将新的ID列再次设置为Int即可。 2. 当表格增长并接近溢出值（20亿）时，您可以查看表格属性、存储中的实际行数。也许您已经接近溢出，但是您的行数远远少于此值。

- MShNajar

Answer 5

为什么有人想要使用BigInt而不是Int作为IDENTITY？

考虑以下情况： 您的数据库存在于多个环境中，包括一个实例在生产环境中，以及其他几个实例在（TestA、B、C等）、（QA A、B、C等）、（Demo A、B、C等）、（UAT A、B、C等）、（Training A、B、C等）等等。你甚至不想知道...

这个数据库的IDENTITY字段用于向第三方提供商传递一个唯一的数字，该提供商是非生产环境中的共享环境。供应商会收取高昂的费用来设置多个环境，因此公司只有一个生产DB和其他所有环境都共用一个。

因此...当在非生产环境中进行测试时，这些数字永远不能从您正在测试的任何非生产环境中相互交叉。而且测试包括压力测试...每次发送数十万行。

最后...所有这些环境都将与生产环境一起刷新，因此身份字段会重置为生产环境中的任何内容。因此，必须跟踪每个环境中使用的传播方式，然后将IDENTITY重置为以前从未使用过的新传播方式。如果在这些环境中再次发送相同的编号，第三方供应商将抛出异常。而且该供应商不愿或无法在其端刷新或重置这些编号。

这是一个现实世界中的问题，当前字段在所有环境中仍为int类型，并且必须每个季度或每当某人进行大规模压力测试100,000多笔交易时更新跟踪这些传播方式的管理。

因此，在约10年内，必须将此IDENTITY更新为BIGINT，否则必须说服第三方供应商在其端刷新。

噢，是的，管理层只在一切突然崩溃时才会关注它。

然后，变通方法“ALTER TABLE tableName ALTER COLUMN ID bigint”就可以胜任。空间和索引处理很便宜！

- Brad Skidmore

---

FK怎么样？他们也需要改为bigint吗？ - user2880486

虽然这对于“为什么不”来说还可以，但问题是“如何”并且引用你的话，“大约在10年内……”好了，10年过去了，现在已经有2.7B行，是时候开始行动了。 - Mark Schultheiss

还有另一个问题，那么这样做会有什么后果？ - Brad Skidmore

“后果？谁在乎后果，只要我有钱！”这是上一次合作中管理层的态度。近视眼在这个行业几乎和自大一样普遍。 - Brad Skidmore