更改表更改列更改数据类型的 SQL Server 性能 [英] SQL Server performance for alter table alter column change data type

问题描述

我们需要将某些列的数据类型从 int 更改为 bigint.不幸的是，其中一些表很大，大约有 7 到 1000 万行(但不宽).

We need to change the data types of some columns from int to bigint. Unfortunately some of these tables are large, around 7-10 million rows (but not wide).

Alter table alter column 在这些表上永远占用.有没有更快的方法来实现这一目标?

Alter table alter column is taking forever on these tables. Is there a faster way to achieve this?

推荐答案

巧合的是，大约 3 小时前我不得不做一些非常相似的事情.这张桌子有 3500 万行，相当宽，而且要花很长时间才能做到这一点:

Coincidentally, I had to do something very similar about 3 hours ago. The table was 35m rows, it is fairly wide, and it was taking forever to just do this:

alter table myTable add myNewColumn int not null default 0;

这是我最终的结果:

alter table myTable add myNewColumn int null;

while 1=1
begin
    update top (100000) myTable
    set
        myNewColumn = 0
    where
        myNewColumn is null;

    if @@ROWCOUNT = 0 break;
end

alter table myTable alter column myNewColumn int not null;
alter table myTable add constraint tw_def_myNewColumn default (0) for myNewColumn;

这一次，alter table 语句几乎是即时的.执行批量更新大约需要 7-8 分钟(在慢速服务器上).我推测 SQL Server 正在我的原始查询中生成撤消以恢复值，但我没想到会开始.

This time around, the alter table statements were near-instant. It took about 7-8 minutes (on a slow server) to do the update batches. I'm speculating that SQL Server was generating undo in my original query to restore the values, but I didn't expect that starting off.

无论如何，就您而言，也许类似的东西会有所帮助.您可以尝试添加一个新的 bigint 列，批量更新新列，然后对其设置约束.

Anyway, in your case, maybe something similar would help. You could try adding a new bigint column, update the new column in batches, then set the constraints on it.

这篇关于更改表更改列更改数据类型的 SQL Server 性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！