如何加快在 Sql Server 中向大表中添加列

嗯，1000 万行是很少的，但它不在 MSSQL 的范围之外，而且看起来确实很慢。

我们有一张表，行数很大（设计不佳），行数超过 1000 万行。当我们不得不修改结构时，它是def。非常慢，所以我们所做的是（保持表格在线，这在记忆中很粗略，因为这是很久以前的事了）：

• 使用后缀“C”（用于转换）和新结构（即与旧表相同，但具有新列/索引/等）创建新表
• SELECT * INTO tableC FROM table
• sp_rename 'table' 'tableOld'
• sp_rename 'tableC' 'table'

这样，转换需要多长时间都没有关系，因为旧数据是在线的。它可能会导致在转换发生时将行写入表的问题（这对我们来说不是问题，因为数据每天只写入一次，但每小时查询数千次）所以你可能想调查一下.

您可以尝试在单独的批次中执行操作的每个步骤，例如

alter table T add mycol bit null
go
update T set mycol = 0
go
alter table T alter column mycol bit not null
go
alter table T add default 0 for mycol
go

优点是：

• 您可以获得有关操作进度的更好反馈，因为它现在是 4 个单独的批次，每个批次大约需要 1/4 的时间。
• 从客户端代码运行它时，它减少了超时错误的可能性。
• 我发现它有时会提高性能。

您还可以尝试在进行更改之前删除表上的所有非聚集索引，然后再恢复它们。添加列很可能涉及大规模的页面拆分或其他低级别的重新排列，并且您可以在进行时无需更新非聚集索引的开销。

根据您的行大小、表大小、索引等，我已经看到 SQL Server 2000 在最终完成之前花费了几个小时（4-5 个小时）。

你现在能做的最糟糕的事情就是“恐慌”和硬杀它。让它自己跑出来。

将来，您可能希望尝试执行 Farseeker 提到的操作并创建第二个（空）结构并通过这种方式复制您的记录。

• 表格行越长，所需的时间就越长。
• 该表上的索引越多，所需的时间就越长。
• 如果您添加默认值（您已添加），则需要更长的时间。
• 如果您在服务器上大量使用，则需要更长的时间。
• 如果您不锁定该数据库或将其置于单用户模式，则需要更长的时间。

当我不得不做这样丑陋的事情时，我会尝试在晚上做......比如凌晨 2 点，没有人在上面（并且维护不在服务器上运行）。

祝你好运！:-)

这将需要相当长的时间。这是因为您要添加默认值。这导致 SQL Server 更新单个事务中的所有行。确保没有其他人在使用该表，因为这将导致您的进程阻塞。

我在一个至少有 6500 万行的表中做了类似的事情，而且没花那么长时间。你的磁盘系统有足够的内存和足够的性能吗

如果您想加快进程，您可以在更改表之前删除所有索引（execpt 聚集索引和外键约束），但必须在系统不使用时完成，否则您最终可能会得到不一致的数据。但是最后你需要在完成之前应用外键和索引，但是你会减轻事务日志的痛苦，至少如果你在简单的恢复模式下运行的话。在 SQL Server 2008 中，您可以使用 ONLINE=on 和 SORT_IN_TEMPDB=on 构建索引

哈坎·温瑟

您并不会真正使用这样的快捷方式——无论您做什么，SQL Server 都必须对表中的所有行进行一些处理。

您可以通过确保您的数据文件和日志位于不同的驱动器和其他常用建议上来确保它尽可能快地运行。

10m 行的时间太长了。检查桌子上没有任何东西打开锁。

在一次培训课程中，我与 DoD 的几位 DBA 进行了交谈。他们管理 100TB 甚至更多的 MySQL 数据库。表更改是通过转储和加载完成的，但这显然需要一些停机时间。他们还提到他们不喜欢对超过 10TB 的数据库执行此操作，因为需要时间。

数据被转储了，他们没有指定要做什么，但我假设是 SQL 文件。然后截断表并根据需要更改架构。然后重新加载数据。

您的表是否碰巧有多个索引，甚至可能是表 T 上的聚集索引？

我也有添加一个新列的问题（它是一个标识列）。该表有 930 万行，并且在主键上有一个非聚集索引。

出于某种原因，如果我们删除表 T 的索引，然后添加列，然后再添加表 T 的索引。在 Standard SQLServer 2008 上它基本上快了 60 倍。

我还没有弄清楚为什么它加速了这么多，希望有人能给我答案。