sql-server - SQL Server 在大表上更改数据类型的最快方法

Question

我们需要将数据库中大约 10 个主键的数据类型从 numeric(19,0) 更改为到 bigint。在较小的表上，数据类型的简单更新工作得很好，但在较大的表(60-7000 万行)上，它需要相当长的时间。

实现这一目标的最快方法是什么，最好不要锁定数据库。

我编写了一个生成以下内容的脚本(我相信我是从不同的 SO 帖子中获得的)

--Add a new temporary column to store the changed value.
ALTER TABLE query_log ADD id_bigint bigint NULL;
GO

CREATE NONCLUSTERED INDEX IX_query_log_id_bigint ON query_log (id_bigint)
INCLUDE (id); -- the include only works on SQL 2008 and up
-- This index may help or hurt performance, I'm not sure... :)

GO

declare @count int
declare @iteration int
declare @progress int
set @iteration = 0
set @progress = 0

select @count = COUNT(*) from query_log
RAISERROR ('Processing %d records', 0, 1, @count) WITH NOWAIT

-- Update the table in batches of 10000 at a time
WHILE 1 = 1 BEGIN 

   UPDATE X -- Updating a derived table only works on SQL 2005 and up
   SET X.id_bigint = id
   FROM (
      SELECT TOP 10000 * FROM query_log WHERE id_bigint IS NULL
   ) X;
   IF @@RowCount = 0 BREAK;
   set @iteration = @iteration + 1
   set @progress = @iteration * 10000
   RAISERROR ('processed %d of %d records', 0, 1, @progress, @count) WITH NOWAIT
END;
GO

--kill the pkey on the old column
ALTER TABLE query_log
DROP CONSTRAINT PK__query_log__53833672
GO

BEGIN TRAN; -- now do as *little* work as possible in this blocking transaction
UPDATE T -- catch any updates that happened after we touched the row
SET T.id_bigint = T.id
FROM query_log T WITH (TABLOCKX, HOLDLOCK)
WHERE T.id_bigint <> T.id;
-- The lock hints ensure everyone is blocked until we do the switcheroo

EXEC sp_rename 'query_log.id', 'id_numeric';
EXEC sp_rename 'query_log.id_bigint', 'id';
COMMIT TRAN;
GO

DROP INDEX IX_query_log_id_bigint ON query_log;    
GO

ALTER TABLE query_log ALTER COLUMN id bigint NOT NULL;
GO

/*
ALTER TABLE query_log DROP COLUMN id_numeric;
GO
*/

ALTER TABLE query_log
ADD CONSTRAINT PK_query_log PRIMARY KEY (id)
GO

这对于较小的表非常有效，但对于非常大的表来说非常慢。

请注意，这是为迁移到 Postgres 做准备，而 EnterpriseDB 迁移工具包似乎不理解 numeric(19,0) 数据类型

Answer 1

如果不加锁就无法更改主键。影响最小的最快方法是使用新列和主键创建一个新表，而没有外键和索引。然后按相对于其主键的顺序批量插入数据块。完成后，添加索引，然后返回外键。最后，删除或重命名旧表并将新表重命名为系统预期的表名。

在实践中，您的方法必须根据插入、更新和/或删除的记录数量而有所不同。如果您只是插入，那么您可以在交换之前执行初始加载和表格顶部。

这种方法应该提供最快的迁移、最少的日志以及很少的表和索引碎片。

你要记住，每次修改一条记录，都是在修改数据，修改索引，检查外键。所有这些都在一个隐式或显式事务中。在进行所有更改时，表和/或行将被锁定。即使您的数据库设置为简单日志记录，服务器仍会将所有更改写入日志文件。更新实际上是与插入配对的删除，因此不可能在任何其他过程中防止碎片化。