SQL 将标识列从 int 替换为 bigint

Replace identity column from int to bigint

提问by Matan L

I am using SQL Server 2008, and I have a table that contains about 50 mill rows.

我使用的是 SQL Server 2008，我有一个包含大约 50 行的表。

That table contains a primary identity column of type int.

该表包含类型为 的主要标识列int。

I want to upgrade that column to be bigint.

我想将该列升级为bigint.

I need to know how to do that in a quick way that will not make my DB server unavailable, and will not delete or ruin any of my data

我需要知道如何以快速的方式做到这一点，既不会使我的数据库服务器不可用，也不会删除或破坏我的任何数据

How should I best do it ? what are the consequences of doing that?

我应该怎么做最好？这样做的后果是什么？

采纳答案by marc_s

Well, it won't be a quick'n'easy way to do this, really....

嗯，这不是一个快速简单的方法，真的......

My approach would be this:

我的方法是这样的：

1. create a new table with identical structure - except for the IDcolumn being BIGINT IDENTITYinstead of INT IDENTITY

   ----[ put your server into exclusive single-user mode here; user cannot use your server from this point on ]----

2. find and disable all foreign key constraints referencing your table

3. turn SET IDENTITY_INSERT (your new table) ON

4. insert the rows from your old table into the new table

5. turn SET IDENTITY_INSERT (your new table) OFF

6. delete your old table

7. rename your new table to the old table name

8. update all table that have a FK reference to your table to use BIGINTinstead of INT(that should be doable with a simple ALTER TABLE ..... ALTER COLUMN FKID BIGINT)

9. re-create all foreign key relationships again

10. now you can return your server to normal multi-user usage again

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. 更新所有对您的表有 FK 引用的表，BIGINT而不是使用INT（这应该可以通过简单的ALTER TABLE ..... ALTER COLUMN FKID BIGINT）

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

10. 现在您可以再次将您的服务器恢复到正常的多用户使用状态

回答by MobileMon

What am I missing?

我错过了什么？

Why can't you just do this:

为什么你不能这样做：

ALTER TABLE tableName ALTER COLUMN ID bigint

I guess try it in a test environment first but this always works for me

我想先在测试环境中尝试一下，但这总是对我有用

回答by Aaron Bertrand

Probably the best way is to create a new table with a BIGINT IDENTITY column, move the existing data using SET IDENTITY_INSERT ON; and then rename the tables. You will need to do this during a maintenance window, just as you would if you changed the data type in Management Studio (which would similarly create a new table, move the data, and block everyone in the process).

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

回答by Brad Skidmore

Why would someone want to use a BigInt instead of Int as an IDENTITY?

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

Consider this scenario: Your database exists in several environments including 1 instance in a live Production environment and several other instances in (TestA, B, C, etc.), (QA A, B, C, etc.), (Demo A, B, C, etc), (UAT A, B, C, etc.), (Training A, B, C, etc.) on and on and on... You don't even want to know...

考虑这种情况：您的数据库存在于多个环境中，包括实时生产环境中的 1 个实例和（TestA、B、C 等）、（QA A、B、C 等）、（演示 A、 B、C 等）、（UAT A、B、C 等）、（训练 A、B、C 等）……您甚至不想知道……

This database IDENTITY field is used to pass in an unique number to a 3rd party provider which is a shared environment in the Non Production environments. The vendor charges an arm and a leg in order to set up multiple environments so the company has one for the production DB and one for ALL the others.

此数据库 IDENTITY 字段用于将唯一编号传递给作为非生产环境中的共享环境的第 3 方提供商。供应商收取一定费用以建立多个环境，因此该公司有一个用于生产数据库，一个用于所有其他环境。

So... when testing happens in the non production environments these numbers can never cross each other from whatever non production environment you happen to be testing in. And the testing includes stress testing... sending 100's of thousands of rows at a time.

所以......当测试在非生产环境中进行时，这些数字永远不会与你碰巧正在测试的任何非生产环境相互交叉。测试包括压力测试......一次发送数百行。

To top it off... ALL these environments get refreshed with Production so the Identity field gets reset with whatever was in production. So one has to keep track of what spread was used in each environment and then reset the IDENTITY to a new spread that has never been used before. The 3rd party vendor will puke if an already number gets sent again in these environments. And the vendor is unwilling or unable to refresh or reset these numbers on their end.

最重要的是......所有这些环境都使用生产进行了刷新，因此身份字段将使用生产中的任何内容进行重置。因此，必须跟踪每个环境中使用的传播，然后将 IDENTITY 重置为以前从未使用过的新传播。如果在这些环境中再次发送已经发送的号码，第 3 方供应商将会吐槽。供应商不愿意或无法刷新或重置这些数字。

This is a real world issue and the current field remains to be an int in ALL environments and the management of keeping track of these spreads is updated every quarter or whenever someone does a massive stress testing 100's of thousands of transactions.

这是一个现实世界的问题，当前字段在所有环境中仍然是 int，并且每季度或每当有人对 100 万笔交易进行大规模压力测试时，都会更新跟踪这些价差的管理。

So in about 10 years this IDENTITY will have to be updated to a BIGINT or someone will have to convince the 3rd party vendor to refresh on their end.

因此，大约 10 年后，此 IDENTITY 将必须更新为 BIGINT，否则必须有人说服第 3 方供应商在其最终更新。

Oh yeah, management could give a rat's ass about it until everything comes crashing down all of a sudden.

哦，是的，管理层可能会对此嗤之以鼻，直到一切都突然崩溃。

Then the HACK "ALTER TABLE tableName ALTER COLUMN ID bigint" will do just fine. Space and index processing is CHEAP!

然后 HACK "ALTER TABLE tableName ALTER COLUMN ID bigint" 就可以了。空间和索引处理很便宜！