sql-server - 将包含数十亿行的表的数据类型从 int 更改为 bigint

我有几张表，其中有几百万行，在某些表中有数十亿行，其中一列作为 int 现在我要更改为 bigint。我尝试使用 SSMS 更改数据类型，但在几个小时后由于事务日志已满而失败。

我采用的另一种方法是创建一个新列并开始将值从旧列批量更新到新列，通过将 ROWCOUNT 属性设置为 100000，它可以工作，但速度很慢，并且它要求完整的服务器内存。使用这种方法，可能需要几天时间才能完成，并且在生产中是 Not Acceptable 。

更改数据类型的快速\最佳方法是什么？源列不是标识列并且是重复的，并且允许为空。该表在其他列上有索引，禁用索引是否会加快进程？添加 Begin Tran 和 Commit 会有帮助吗？

最佳答案

我对 ALTER COLUMN 进行了测试，显示了进行更改所需的实际时间。结果表明 ALTER COLUMN 不是瞬时的，所需的时间呈线性增长。

RecordCt    Elapsed Mcs
----------- -----------
      10000      184019
     100000     1814181
    1000000    18410841

我的建议是按照你的建议批量处理。创建一个新列，并随着时间的推移使用 ROWCOUNT 和 WAITFOR 的组合预先填充该列。

编写脚本以便从表中读取 WAITFOR 值。这样您就可以在生产服务器开始出现故障时即时修改 WAITFOR 值。您可以在非高峰时段缩短 WAITFOR。 (您甚至可以使用 DMV 使您的 WAITFOR 值自动进行，但这当然更复杂。)

这是一个复杂的更新，需要规划和大量的保姆。

抢

这是 ALTER COLUMN 测试代码。

USE tempdb;
SET NOCOUNT ON;
GO
IF EXISTS (SELECT * FROM sys.tables WHERE [object_id] = OBJECT_ID('dbo.TestTable'))
    DROP TABLE dbo.TestTable;
GO
CREATE TABLE dbo.TestTable (
    ColID   int              IDENTITY,
    ColTest int              NULL,
    ColGuid uniqueidentifier DEFAULT NEWSEQUENTIALID()
);
GO

INSERT INTO dbo.TestTable DEFAULT VALUES;
GO 10000

UPDATE dbo.TestTable SET ColTest = ColID;
GO

DECLARE @t1 time(7) = SYSDATETIME();
DECLARE @t2 time(7);

ALTER TABLE dbo.TestTable ALTER COLUMN ColTest bigint NULL;

SET @t2 = SYSDATETIME();

SELECT
    MAX(ColID)              AS RecordCt,
    DATEDIFF(mcs, @t1, @t2) AS [Elapsed Mcs]
FROM dbo.TestTable;